XLOOKUP
Find a row by one value, bring back another
0 of 3 done · Lesson 10 of 18
Understand
Someone drops an order number on your desk and asks which region it came from. The sheet has 8,000 rows and you are not scrolling through them. XLOOKUP finds the row for you and brings back the value you asked for.
=XLOOKUP(who to find, where to look, what to bring back, what to show instead, optional)
A worked example
=XLOOKUP(F1, A2:A9, B2:B9)
who to findwhere to lookwhat to bring backwhat to show instead
This returns Northwind
The sheet you are working with
| Row number | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Order | Customer | Region | Amount | Find order | 1047 |
| 2 | 1042 | Northwind | Otago | 1250 | Also find | 1051 |
| 3 | 1043 | Kea Ltd | Waikato | 840 | ||
| 4 | 1044 | Halcyon | Otago | 2110 | ||
| 5 | 1045 | Brightsmith | Auckland | 560 | ||
| 6 | 1046 | Tuatara | Waikato | 1780 | ||
| 7 | 1047 | Northwind | Otago | 930 | ||
| 8 | 1048 | Pounamu | Nelson | 1420 | ||
| 9 | 1049 | Rimu Co | Waikato | 670 |