Home › Excel functions › INDEX/MATCH
INDEX MATCH in Excel: the INDEX and MATCH Functions Explained
INDEX and MATCH together form a lookup that can do everything VLOOKUP can, and more: look to the left, survive inserted columns and search rows and columns at the same time. It works in every version of Excel.
INDEX/MATCH · In this guide
How INDEX and MATCH work
The combination is easiest to understand as two separate functions with one job each.
MATCH finds the position
=MATCH(lookup_value, lookup_array, [match_type])MATCH returns the position of a value in a list, not the value itself. Use 0 as the last argument for an exact match.
INDEX returns the value at a position
=INDEX(array, row_num, [column_num])INDEX returns whatever sits at a given position in a range. =INDEX(D2:D6, 4) returns the 4th value in D2:D6.
Together
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))MATCH finds which row the value is in, and INDEX uses that row number to fetch the result from another column.
Step-by-step example
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Product | Stock | Price |
| 2 | P-101 | Apple | 120 | $0.35 |
| 3 | P-102 | Banana | 85 | $0.25 |
| 4 | P-103 | Cherry | 300 | $0.10 |
| 5 | P-104 | Mango | 40 | $1.20 |
| 6 | P-105 | Pear | 60 | $0.30 |
=MATCH("Mango", B2:B6, 0)returns 4: Mango is the 4th item in B2:B6.=INDEX(D2:D6, 4)returns $1.20: the 4th item in D2:D6.- Put them together:
=INDEX(D2:D6, MATCH("Mango", B2:B6, 0))returns $1.20.
Keep both ranges the same size and starting row. If the lookup range starts at row 2, the return range must too. Otherwise the position from MATCH points to the wrong row.
Look up a value to the left
Because the lookup range and return range are separate, the return column can be anywhere. To get the SKU of the mango from column A:
=INDEX(A2:A6, MATCH("Mango", B2:B6, 0))This returns P-104, something VLOOKUP can't do.
Two-way lookup: row and column
Give INDEX a range several columns wide and a second MATCH for the column. This finds the value where the "Mango" row meets the "Price" column:
=INDEX(C2:D6, MATCH("Mango", B2:B6, 0), MATCH("Price", C1:D1, 0))The first MATCH returns row 4, the second returns column 2, so INDEX returns $1.20. Change "Price" to "Stock" and you get 40. This is ideal for tables like sales per month per region.
Why use INDEX/MATCH instead of VLOOKUP?
- It can look left. The return column doesn't have to be to the right of the lookup column.
- It doesn't break when you insert columns. VLOOKUP's fixed column number (like 3) points to the wrong column after you insert one; INDEX/MATCH references move with the data.
- It can search in two directions with a two-way lookup.
- It works everywhere, including Excel 2019 and older, where XLOOKUP isn't available.
In Microsoft 365 or Excel 2021 and later, XLOOKUP does the same in one function and is easier to read. INDEX/MATCH is still everywhere in existing spreadsheets, so it's worth knowing.
Common errors
#N/A
MATCH couldn't find the value. Check spelling, extra spaces and numbers stored as text. Wrap the formula in IFERROR to show a message instead.
Wrong result, no error
You probably left out the 0 in MATCH. Without it, MATCH does an approximate match that only works on sorted data. Or the two ranges don't start on the same row.
#REF!
The row or column number is larger than the INDEX range, usually because the INDEX range is smaller than the MATCH range.
Frequently asked questions
Is INDEX MATCH faster than VLOOKUP?
On very large sheets it can be slightly faster, because it only reads the two columns it needs. For most everyday workbooks you won't notice a difference.
What does the 0 in MATCH mean?
It sets the match type to exact match. Use 1 (or leave it out) for "largest value less than or equal to" on data sorted in ascending order, and -1 for "smallest value greater than or equal to" on descending data.
Should I use INDEX MATCH or XLOOKUP?
Use XLOOKUP if everyone has Microsoft 365, Excel 2021 or later. Use INDEX MATCH when the file must work in older versions.
Can INDEX MATCH use multiple criteria?
Yes. In Microsoft 365 you can write =INDEX(D2:D6, MATCH(1, (B2:B6="Mango")*(C2:C6>20), 0)). In older versions, confirm it as an array formula with Ctrl+Shift+Enter.