Excel track · Exercise 46 of 86 Paid
INDEX MATCH: look left of the key
Return a value from the left of the key with INDEX and MATCH.
Intermediate
Four priced products
Read-only data · answer in B7
In B7, return the product name in A2:A5 for the SKU in B6 with INDEX and MATCH.
| A | B | |
|---|---|---|
| 1 | Product | Unit price |
| 2 | Cable reel | 8.5 |
| 3 | Junction box | 3.25 |
| 4 | Conduit | 2.75 |
| 5 | Trunking | 5.4 |
| 6 | Which price? | 3.25 |
| 7 | Product |
Reference answer
What it does
Here the answer sits to the left of the value being searched. B6 holds the price 3.25, which is the second entry in B2:B5, so MATCH returns 2. INDEX then reads the second cell of A2:A5, and B7 shows Junction box.
VLOOKUP cannot make that move. Its column count starts at the key and runs forward, so no argument reaches a column sitting before the key. Choosing the two ranges separately is what lets the lookup run left.
The zero matters even more on this sheet. The price column runs 8.5, 3.25, 2.75, 5.4, which is not sorted in either direction. Approximate search stops somewhere in that order and can read a neighbouring row. With 0, a price that is not in the list gives #N/A.
Syntax
=INDEX(range, MATCH(value, list, 0))Worked example
On Four priced products, =INDEX(A2:A5,MATCH(B6,B2:B5,0)) returns Junction box.
=INDEX(A2:A5,MATCH(B6,B2:B5,0))Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | MATCH found no exact 3.25 in B2:B5, so INDEX was handed a position that does not exist. |
#REF! | INDEX was pointed at a range shorter than the position MATCH returned, so the row number runs past A5. |
Wrong product | The zero was left off, so MATCH accepted a price it never found and INDEX read a neighbouring product. |
At work
A price list where you know what something cost and need the part name, which sits to the left of the price.