Lookup
A two-step lookup
Definition
INDEX and MATCH together first locate a position with MATCH and then fetch the value at that position with INDEX.
Syntax
=INDEX(return_range, MATCH(key, lookup_range, 0))
Arguments
| Argument | Meaning |
|---|---|
return_range |
The column or row holding the value you want back. |
key |
The item you are searching for. |
lookup_range |
The column or row that contains the key, the same size as the return range. |
0 |
Makes MATCH look for an exact match. |
Worked example
A price list keeps the price in column A and the product in column B, so the key sits to the right of the answer.
| A: Price | B: Product |
|---|---|
| 29 | Kettle |
| 68 | Blender |
| 53 | Toaster |
Formula: =INDEX(A2:A4,MATCH("Toaster",B2:B4,0))
Result: 53. MATCH finds Toaster at position 3, and INDEX returns the third price, 53, even though it lies to the left of the key.
Practice
Cities Quito, Lima, and Bogota sit in B2:B4, and their elevations in metres, 2850, 154, and 2640, sit in A2:A4. In D2, write a formula that returns the elevation of Bogota.
- In D2, write an INDEX and MATCH formula that returns the elevation of Bogota. Elevations are in A2:A4 and cities are in B2:B4.
- D2 should show the elevation of Bogota.
Related functions
INDEX and MATCH split a lookup into two steps, so each can be checked on its own and either can be reused elsewhere.
Common mistake
Using ranges that do not line up, such as INDEX(A2:A4,MATCH(...,B3:B5,0)); the position is off by one row and the wrong item comes back.