AdvancedData Cleaning
Find which customers are missing from a list
Use COUNTIF to reconcile two lists and spot what is absent.
COUNTIF · IF
14 min40 points
Why does a lookup fail on clean data?
The situation
An order number in your sheet reads 100234. The same order number in the export reads 100234. They look identical, they are the same length, and XLOOKUP still returns #N/A on every row.
Functions in this exercise
- XLOOKUP — Finds a value in one range and returns the matching value from another — in any direction.
Who this is for
Recommended next
Related exercises
- AdvancedData Cleaning
Reconcile a list with hidden spaces
Match two lists where one side has trailing spaces you cannot see.
COUNTIF · IF
12 min35 points - BeginnerLookup & Reference
What is Excel telling you with #N/A?
Read an error code as a diagnosis rather than a failure.
3 min15 points - IntermediateLookup & Reference
Look up a customer's region with XLOOKUP
Match a customer ID against a reference table to return their region.
XLOOKUP
8 min25 points