Excel track · Exercise 38 of 86 Paid
XLOOKUP: exact match, no column counting
Return a value from a column anywhere in the table, with no column number to count.
Intermediate
SKU price list with cost and price columns
Read-only data · answer in B6
In B6, return the price of the SKU in A6 with XLOOKUP, whose third argument is the answer column itself rather than a column number.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Product | Cost | Price |
| 2 | TX-101 | Widget | 12.5 | 19.99 |
| 3 | TX-102 | Gizmo | 7.25 | 14 |
| 4 | TX-103 | Sprocket | 4.8 | 9.5 |
| 5 | TX-104 | Flange | 21 | 39 |
| 6 | TX-103 |
Reference answer
What it does
XLOOKUP reads two ranges side by side. TX-103 is the third entry of A2:A5, so the answer is the third entry of D2:D5, which is 9.5. The cost column between them plays no part.
The third argument is the answer column itself, D2:D5, not a number. That is why the cost column can sit between the keys and the prices, and why pointing at C2:C5 returns 4.8 instead. The two ranges must be the same size, and each key must sit beside its answer.
D1:D4 starts one row too high and returns 14, because its third price is really the second. Match mode defaults to exact, so the last two arguments can stay empty.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Worked example
On SKU price list with cost and price columns, =XLOOKUP(A6,A2:A5,D2:D5) returns 9.5.
=XLOOKUP(A6,A2:A5,D2:D5)Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | The SKU is not in the lookup range, which happens when the range stops early or points at the product names. |
#VALUE! | The lookup and return ranges are different sizes, so a price has no SKU to pair with. |
At work
Price lists and rate cards where the key and the value are not neighbours, or where someone will reorder the sheet next week.