Excel track · Exercise 42 of 86 Paid
Lookup functions summary
Pick the lookup that can return a column to the left of its key.
Intermediate
Products left of their SKUs
Read-only data · answer in B7
In B7, return the product name for the SKU in B6, even though the name column sits left of the key.
| A | B | C | |
|---|---|---|---|
| 1 | Product | SKU | On hand |
| 2 | Bracket | BX-210 | 240 |
| 3 | Clamp | CX-330 | 96 |
| 4 | Dowel | DX-410 | 60 |
| 5 | Extrusion | EX-500 | 18 |
| 6 | Which SKU? | DX-410 | |
| 7 | Product |
Reference answer
What it does
Four functions do this job: VLOOKUP, HLOOKUP, INDEX with MATCH, and XLOOKUP. VLOOKUP reads right from the key column and HLOOKUP reads down from the key row. INDEX with MATCH reaches either way, at the cost of two functions.
This sheet puts the product name left of the SKU, which is the case that separates them. VLOOKUP cannot return a column left of its first column. XLOOKUP names B2:B5 to search and A2:A5 to return, and the leftward move costs nothing.
Choose by where the answer sits. Right of the key, VLOOKUP is one function and one number. Left of it, XLOOKUP names the column without counting.
Syntax
=XLOOKUP(value, search column, return column)Worked example
On Products left of their SKUs, =XLOOKUP(B6,B2:B5,A2:A5) returns Dowel.
=XLOOKUP(B6,B2:B5,A2:A5)Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | Nothing in the searched column matched, so the lookup has no row to return from. |
#REF! | A VLOOKUP column number counted past the returned range, so it points at a column that is not there. |
At work
A stock file exported by a till, where the code sits to the right of a description nobody wants to retype.