How to use INDEX MATCH in Excel
INDEX MATCH is a pair of functions that look up a value in a table. MATCH finds the position of what you are looking for, and INDEX returns the cell at that position from another column. The pair works in every version of Excel, can look to the left, and keeps working when you insert a column.
Below is the pattern, a lookup to the left, and a two-way lookup. At the end are the lessons where you can practice it.
1. The pattern
The pattern is =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). The final 0 asks for an exact match, which is what you want almost every time. Both ranges must cover the same rows, and the return range is the column you want the answer from.
Example: The price of Ink
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Name | Price | Look up | Price | |
| 2 | 101 | Pen | 2.5 | Ink | 7 | |
| 3 | 102 | Ink | 7 | |||
| 4 | 103 | Pad | 4 |
MATCH finds Ink in the second position of B2:B4, and INDEX returns the second cell of C2:C4, which is 7.
2. Look up to the left
VLOOKUP can only return a column to the right of the lookup column. With INDEX MATCH you simply point the return range at any column, even one before the lookup column.
Example: The ID of Ink
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Name | Price | Look up | ID | |
| 2 | 101 | Pen | 2.5 | Ink | 102 | |
| 3 | 102 | Ink | 7 | |||
| 4 | 103 | Pad | 4 |
The ID column is to the left of the Name column. INDEX does not mind and returns 102.
3. A two-way lookup
To find the value where a row and a column meet, give INDEX the whole table and two MATCH functions: the first finds the row, the second finds the column.
Example: Ink in February
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Jan | Feb | Mar | Result | |
| 2 | Pen | 5 | 6 | 7 | 9 | |
| 3 | Ink | 8 | 9 | 10 |
Ink is the second row and Feb is the second column of B2:D3, so INDEX returns 9.
4. INDEX MATCH or XLOOKUP
In Microsoft 365 and Excel 2021 or later, XLOOKUP does the same job with one function and is easier to read. INDEX MATCH is still worth knowing because it works in older files, in Google Sheets and in the workbooks you will inherit. See the comparison in the guide on VLOOKUP and XLOOKUP.
Common mistakes
Leaving out the 0. Without the last argument MATCH assumes a sorted list and may return a wrong row.
Fix: Always end the MATCH with 0 for an exact match.
Ranges that start in different rows. If one range starts at row 2 and the other at row 3, every answer is off by one row.
Fix: Select both ranges with the same first and last row.
An #N/A result. The lookup value is not in the list, often because of an extra space or a number stored as text.
Fix: Clean the data with TRIM, or wrap the formula in IFERROR to show a message instead.
Frequently asked questions
Is INDEX MATCH faster than VLOOKUP?
On large tables it can be, because it reads only two columns. For everyday sheets the difference does not matter, so choose the one that is easier for you to read.
Can INDEX MATCH return several columns?
Give INDEX a return range of several columns and use 0 as the column number, or use XLOOKUP, which returns several columns directly in Microsoft 365.
What does the 0 in MATCH mean?
It is the match type. 0 asks for an exact match, 1 for the largest value that is not greater than the lookup value in a sorted list, and -1 for the smallest value that is not less than it in a descending list.
Related functions
- INDEXINDEX returns the value at a given row and column of a range.
- MATCHMATCH returns the position of a value in a list, for example that "Lena" is the 3rd name.
- VLOOKUPVLOOKUP finds a value in the first column of a table and returns a value from the same row in another column.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.
- XMATCHXMATCH returns the position of a value in a list, with exact match by default, approximate matches and a search from the last item.
More on INDEX and MATCH
The guide gives the pattern and two worked lookups. This piece adds why the pair survives column changes, how to look up with two conditions, and how to read an INDEX MATCH formula that someone else wrote.
Why it survives inserted columns
A VLOOKUP asks for a column number, such as 3. If someone inserts a column in the table, the number is now wrong and the formula quietly returns the neighbor. INDEX MATCH points at whole ranges, and Excel moves a range when a column is inserted, so the formula keeps returning the right column.
=INDEX(C2:C100,MATCH(F2,B2:B100,0))Look up with two conditions
To find a row by two values, such as a region and a product, make MATCH search a combined key. Join the two columns with & inside MATCH and join the two lookup cells the same way. In older versions of Excel this must be entered as an array formula, with Ctrl+Shift+Enter. In Microsoft 365 it works as a normal formula.
=INDEX(C2:C100,MATCH(F1&F2,A2:A100&B2:B100,0))If the table is large and the formula slows the sheet, add a helper column that joins the two keys once, and match against that column.
Read an INDEX MATCH formula
Read it from the inside out. First look at the MATCH: what is it looking for, in which range, and does it end with 0? Then look at the INDEX: which range does the answer come from, and does it start in the same row as the lookup range? Most broken formulas have two ranges that start in different rows.
If you have Microsoft 365, compare the same lookup with XLOOKUP, and read [how VLOOKUP and XLOOKUP differ](/guides/vlookup-vs-xlookup). To practice the pair itself, open [the INDEX MATCH lesson](/learn/index-match).