Skip to content
AnIntent

How to Use INDEX and MATCH in Excel (Look Left, Exact Match, Fix #N/A)

Combine INDEX and MATCH to look up a value in any column, force an exact match with 0, and fix the #N/A and #REF! errors.

AnIntent Editorial · · 4 min read

Person working on a laptop with a spreadsheet outdoors
Photo by Gorilla ROI Data Connector on Unsplash
In this article
  1. What each function does on its own
  2. Build the INDEX MATCH formula
  3. Look up a value to the left of the key column
  4. Always set match_type to 0 for an exact match
  5. Fix #N/A from INDEX MATCH
  6. Fix #REF! from INDEX
  7. What this guide does not cover
Share this article

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

  1. Pick the column you want the answer from. This is the range INDEX will read.
  2. Pick the column that holds your key, such as an ID or product code. This is MATCH's lookup_array.
  3. Write MATCH with 0 as the last argument so it only accepts an exact match.
  4. 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:

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.

Common questions

What happens if I leave out the match_type in MATCH?

Microsoft's MATCH page says omitting it behaves like 1: MATCH finds the largest value less than or equal to the lookup value, which is an approximate match.

Is INDEX MATCH case-sensitive?

No. Microsoft notes that MATCH does not distinguish upper case from lower case for text.

Why does INDEX MATCH return #REF!?

INDEX returns #REF! when row_num or column_num points outside its range. Make sure the INDEX range covers the same rows as the MATCH range.

Can INDEX MATCH look up a value to the left?

Yes. INDEX and MATCH use separate ranges, so the column you return from can be on either side of the key column.

laptop computer on glass-top table
How-to

How to Fix the #SPILL! Error in Excel: Find the Cause, Then the Fix

Learn how to tell which cause is behind Excel's #SPILL! error, whether it's a blocked range, a volatile size or a whole-column lookup, and how to fix each.

· 5 min

More in Cloud & Productivity Software