Excel · Week 3 of 4 · 5–6 hours
Lookup Functions and Data Cleaning
Bring information together from separate tables, and make messy data trustworthy.
Learning objectives
By the end of this week you will be able to:
- Explain what a lookup does and when you need one.
- Use XLOOKUP (and read VLOOKUP) to pull matching values from another table.
- Find and fix common data quality problems: duplicates, extra spaces, inconsistent spelling and blanks.
- Use TRIM, PROPER, Remove Duplicates, Find and Replace and Text to Columns.
Lessons
Lookup Functions
Real data is often split across tables. Your sales sheet might list a Product ID, while the price and category live in a separate Products sheet. A lookup finds a value in one table and returns a matching value from another, like looking up a name in a phone book to find the number.
XLOOKUP (Excel 2021 and Microsoft 365) is the modern choice:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Example: =XLOOKUP(C2, Products!A:A, Products!C:C, "Not found") takes the Product ID in C2, finds it in column A of the Products sheet, and returns the price from column C.
VLOOKUP is older but still everywhere, so you need to be able to read it:
=VLOOKUP(lookup_value, table, column_number, FALSE)
=VLOOKUP(C2, Products!A:C, 3, FALSE) does the same job. Two rules trip beginners up: the value you look up must be in the first column of the table, and you should almost always end with FALSE (exact match). Without it, VLOOKUP may return a wrong value silently.
When a lookup returns #N/A, the value was not found. Usual causes: extra spaces, a number stored as text in one table and as a number in the other, or a spelling difference. That is why data cleaning comes next.
Data Cleaning
Analysts often say that most of the job is cleaning data. Results built on messy data are wrong, even if every formula is right. Check for these problems every time:
| Problem | Example | Fix |
|---|---|---|
| Extra spaces | " North " | =TRIM(A2) removes leading, trailing and double spaces |
| Inconsistent capitals | north, NORTH, North | =PROPER(A2), =UPPER() or =LOWER() |
| Spelling variants | N. America, North America | Home > Find & Select > Replace |
| Duplicate rows | The same order entered twice | Data > Remove Duplicates |
| Several values in one cell | "Smith, Jane" | Data > Text to Columns, split on the comma |
| Blanks | Missing region | Filter for (Blanks), then decide: fill, flag or remove |
A safe workflow: (1) keep an untouched copy of the raw data on its own sheet; (2) clean in a new sheet or helper columns; (3) when a helper column is right, copy it and use Paste Special > Values to replace the original; (4) write down every change you made. That record lets someone else trust your numbers.
Quick profile: use a filter drop-down on each column to scan the unique values. Typos and odd entries jump out immediately.
Watch
- VideoData Cleaning in Excel - 10 Tricks (Beginner to PRO)
More cleaning tools, including Text to Columns.
Practice
Add a Products sheet with Product, Category and Cost columns. On your Sales sheet:
- Use XLOOKUP to bring in Category and Cost for each row, then calculate Profit.
- Deliberately introduce problems into a copy of the data: two duplicate rows, a region typed as " north ", and one product misspelled.
- Notice which lookups break, then clean the data using TRIM, PROPER, Find and Replace and Remove Duplicates until every lookup works.
- Write a short cleaning log listing each change.
Check your understanding
Try each question first, then open it to see the answer.
What does the FALSE at the end of a VLOOKUP do?
It forces an exact match. Without it VLOOKUP may return a nearby, wrong value.
A lookup returns #N/A but the value looks identical in both tables. Name two likely causes.
Extra spaces, or the value is text in one table and a number in the other.
Why keep an untouched copy of the raw data?
So you can always go back and so others can verify how the clean data was produced.
Assignment
Submit the workbook with the Products sheet, working lookups and a Profit column, plus your cleaning log (a list of at least five changes).