INDEX and MATCH in Excel: the flexible lookup
MATCH finds the position of a value in a list. INDEX returns the value at a position. Together, =INDEX(B2:B7,MATCH("Priya",A2:A7,0)) finds Priya in column A and returns her sale from column B.
The examples use this small sales table, the same one in the practice box below.
| A Rep | B Sales | D Target | E Region | |
|---|---|---|---|---|
| 2 | Dana | 1,250 | 1,000 | East |
| 3 | Luis | 830 | 1,000 | West |
| 4 | Priya | 2,100 | 1,000 | East |
| 5 | Marcus | 475 | 1,000 | North |
| 6 | Jen | 990 | 1,000 | West |
| 7 | Omar | 1,600 | 1,000 | East |
How the two parts work
=MATCH("Priya",A2:A7,0)returns 3, because Priya is the third name in the list. The 0 means exact match.=INDEX(B2:B7,3)returns 2,100, the third value in the sales column.- Put MATCH inside INDEX and you get
=INDEX(B2:B7,MATCH("Priya",A2:A7,0)), which returns 2,100.
Syntax
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))Why people use it
- It can look left. The return range can be anywhere.
- Inserting a column doesn't break it, since there's no column number.
- It works in every version of Excel, unlike XLOOKUP.
Common mistakes
- Leaving off the 0 in MATCH. Without it, MATCH assumes sorted data and may return the wrong row.
- Ranges that start on different rows, like B2:B7 with A1:A7. The positions shift by one.
- Using the whole table in INDEX without a column number. Point INDEX at a single column.
Try it: INDEX and MATCH
In B8, find Priya's sales using INDEX and MATCH (not VLOOKUP).
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
Is INDEX MATCH faster than VLOOKUP?
On normal-sized sheets you won't notice a difference. People choose it because it's more flexible, not faster.
Does INDEX MATCH work in Google Sheets?
Yes, exactly the same way.
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
How VLOOKUP works in Excel, why you should always end it with FALSE, how to lock the table with $, its limits, and a live exercise.
How XLOOKUP works in Excel and Google Sheets, how it compares to VLOOKUP, the built-in not-found value, which versions have it, and a live exercise.
A one-page cheat sheet of the Excel formulas that show up on pre-employment tests, with the syntax, a real example and the mistake to avoid for each.