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
Chain two lookups with XLOOKUP
The situation
Credit control has an order number and needs the payment terms. Nothing links them directly: one table maps orders to customers, another maps customers to terms, and the answer only appears once you have been through both.
A1
fx
| Row number | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Terms | Order | Customer | Customer | Terms | ||||
| 2 | SO-7001 | SO-7001 | Dockside Hardware | Dockside Hardware | Net 30 | |||||
| 3 | SO-7002 | SO-7002 | Fenwick Building | Fenwick Building | Net 45 | |||||
| 4 | SO-7003 | SO-7003 | Alder & Vance | Alder & Vance | Prepaid | |||||
| 5 | SO-7004 | SO-7004 | Riverbend Trade | Riverbend Trade | Net 60 | |||||
| 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
- 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
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