AdvancedLookup & Reference
Chain two lookups with XLOOKUP
Look up a value that only exists two tables away from what you started with.
XLOOKUP
13 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. Syntax and example
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 - AdvancedLookup & Reference
Chain two lookups with XLOOKUP
Look up a value that only exists two tables away from what you started with.
XLOOKUP
13 min40 points