Excel functions / Lookup & reference
INDEX & MATCH
Combines INDEX and MATCH into a flexible lookup that works in any direction and in every version of Excel.
Syntax
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))Arguments
return_range | The column holding the answer you want back. |
|---|---|
lookup_value | What you are searching for. |
lookup_range | The column to search, the same height as the return range. |
How it works
MATCH finds which row the value is in, and INDEX returns the item at that row from another column. Together they do everything VLOOKUP does, with fewer limits.
The answer column can be to the left or right of the search column, and inserting columns never breaks the formula, because there is no column number to keep up to date.
Example
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | Stock | ||
| 2 | A10 | Lamp | $25.00 | 12 | $85.00 | |
| 3 | B20 | Desk | $140.00 | 3 | ||
| 4 | C30 | Chair | $85.00 | 7 | ||
| 5 | D40 | Shelf | $60.00 | 0 | ||
| 6 |
Result: $85.00. MATCH finds Chair at position 3, and INDEX returns the 3rd price.
Tips
- Build it in two steps: write the MATCH first, check the position, then wrap it in INDEX.
Practise INDEX & MATCH
Try it yourself in a real spreadsheet, with instant feedback on every answer.