INDEX and MATCH together let you find a row by its key and return a value from any other column, including one to the left of the key. This guide shows how to build the formula, why the third MATCH argument should almost always be 0, and what to do when you see #N/A or #REF!.
What each function does on its own
MATCH finds a position. Microsoft's MATCH function page says it searches for an item in a range and returns that item's relative position, not the value itself. The syntax is:
MATCH(lookup_value, lookup_array, [match_type])
INDEX returns the value at a position. According to Microsoft's INDEX function page, INDEX returns a value, or a reference to a value, from within a table or range. Its reference form is:
INDEX(reference, row_num, [column_num], [area_num])
Microsoft's own example =INDEX(A2:B3,2,2) returns the value in the second row and second column of A2:B3.
The link between them is spelled out in a tip on the MATCH page: MATCH can supply the row_num argument of INDEX. MATCH works out which row the key sits in, and INDEX pulls the value from that row.
Build the INDEX MATCH formula
- Pick the column you want the answer from. This is the range INDEX will read.
- Pick the column that holds your key, such as an ID or product code. This is MATCH's
lookup_array. - Write MATCH with
0as the last argument so it only accepts an exact match. - Put that MATCH inside INDEX as the row number.
With keys in column A, the answer you want in column C, and the key you are searching for typed in F2, the formula looks like this:
=INDEX(C2:C10, MATCH(F2, A2:A10, 0))
Keep the two ranges the same height and starting on the same row. MATCH returns a position counted from the top of its own range, and INDEX counts from the top of its range, so if one range starts a row lower than the other, you get the wrong row back.
Look up a value to the left of the key column
This is the main reason people reach for INDEX and MATCH over VLOOKUP. Because INDEX and MATCH take separate ranges, nothing ties the answer column to the right of the key column. If the keys are in column C and you want the name in column A, swap the ranges:
=INDEX(A2:A10, MATCH(F2, C2:C10, 0))
If you are deciding between approaches, see our guides to VLOOKUP and XLOOKUP. You will still meet INDEX and MATCH in plenty of older workbooks, so it pays to be able to read it.
Always set match_type to 0 for an exact match
The most common INDEX MATCH mistake is leaving off the last MATCH argument. Microsoft's MATCH documentation lists three settings:
| match_type | What MATCH finds |
|---|---|
1 or omitted |
The largest value less than or equal to lookup_value |
0 |
The first value exactly equal to lookup_value |
-1 |
The smallest value greater than or equal to lookup_value |
So if you leave the argument out, Excel does an approximate match, not an exact one. Microsoft's example shows what that means: with its sample data in B2:B5, =MATCH(39,B2:B5,1) has no exact match for 39, so it returns the position of the next lowest value, 38. In a lookup of IDs or names, that quietly returns the wrong row instead of an error.
The -1 setting has its own trap. Microsoft's example =MATCH(40,B2:B5,-1) returns an error because the values are not in descending order.
For almost every lookup by ID, name or code, type the 0.
Fix #N/A from INDEX MATCH
Microsoft says MATCH returns #N/A when there is no match, and INDEX passes that error straight through. Check these things:
- The key really is missing. Search the lookup column for it.
- Hidden spaces. A trailing space makes two values look the same but not match. Our guide to removing extra spaces with TRIM and CLEAN covers the fix.
- Numbers stored as text. A number in one column and the same digits stored as text in the other will not match. See how to convert text to numbers in Excel.
Case is not the problem: Microsoft notes that MATCH does not distinguish upper case from lower case for text.
If a missing key is expected and you want a friendlier result than #N/A, wrap the formula with IFERROR.
Fix #REF! from INDEX
The INDEX page says INDEX returns #REF! when row_num or column_num points outside the range. With INDEX MATCH, this usually means the INDEX range is shorter than the MATCH range, so MATCH finds a row that INDEX's range does not reach. Make both ranges cover the same rows.
What this guide does not cover
This guide does not cover wildcard matching, multiple-criteria lookups, Excel for the web or older Excel versions, or Google Sheets. See Microsoft's MATCH and INDEX pages for those cases.