AdvancedLookup & Reference
Lock ranges so a lookup survives fill-down
Use absolute references so a lookup does not break when copied down a column.
XLOOKUP
12 min40 points
Handle missing lookups gracefully
The situation
Harbor Supply's report goes to the sales director, and a column of #N/A errors makes it look broken. New customers legitimately have no region assigned yet.
A1
fx
| Row number | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Customer ID | Region | Customer ID | Region | |||
| 2 | ORD-1052 | CUST-999 | CUST-118 | East | ||||
| 3 | ORD-1041 | CUST-204 | CUST-204 | West | ||||
| 4 | ORD-1042 | CUST-118 | CUST-291 | North | ||||
| 5 | CUST-377 | South | ||||||
| 6 |
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
- AdvancedLookup & Reference
Lock ranges so a lookup survives fill-down
Use absolute references so a lookup does not break when copied down a column.
XLOOKUP
12 min40 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 - IntermediateLookup & Reference
Look up a unit price with VLOOKUP
Use VLOOKUP with exact match to pull prices from a product table.
VLOOKUP
8 min25 points