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
Lock ranges so a lookup survives fill-down
The situation
A Harbor Supply analyst built a lookup that worked perfectly on the first row, filled it down, and half the column returned #N/A. You need to write the version that survives.
A1
fx
| Row number | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Customer ID | Region | Customer ID | Region | |||
| 2 | ORD-1041 | CUST-204 | CUST-118 | East | ||||
| 3 | ORD-1042 | CUST-118 | CUST-204 | West | ||||
| 4 | ORD-1043 | CUST-377 | CUST-291 | North | ||||
| 5 | CUST-377 | South | ||||||
| 6 | CUST-402 | West | ||||||
| 7 |
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
- 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 - IntermediateLookup & Reference
Find a value with INDEX and MATCH
Combine INDEX and MATCH to look up a value without a fixed column number.
INDEX · MATCH
10 min25 points - AdvancedLookup & Reference
Handle missing lookups gracefully
Return a readable message instead of #N/A when a lookup finds nothing.
XLOOKUP
10 min40 points