INDEX MATCH in Excel: How It Works
Combine INDEX and MATCH to look up values in any direction. Formula, examples and common mistakes.
The formula
MATCH finds the position of your lookup value in a range. INDEX then returns the item at that position from another range. The 0 means exact match.
Example
You have product names in A2:A50 and prices in C2:C50. To get the price of the product typed in E2:
Look up to the left
Because the return range is separate, it can sit to the left of the lookup range. VLOOKUP cannot do this.
Two criteria
In Excel 365 this works as is. In older versions confirm it with Ctrl+Shift+Enter.
Two-way lookup
The first MATCH finds the row, the second finds the column.
INDEX MATCH vs VLOOKUP vs XLOOKUP
INDEX MATCH does not break when you insert columns and works in every Excel version. XLOOKUP is simpler to write but needs Excel 365 or 2021. See our XLOOKUP guide if you have it.
Common mistakes
Forgetting the 0 for exact match returns wrong results on unsorted data. Ranges of different sizes in the two functions return errors or the wrong row. Extra spaces in the data cause #N/A, so clean it with TRIM.
Frequently asked questions
Is INDEX MATCH better than VLOOKUP?
It is more flexible. It looks left, survives column inserts and is faster on large sheets, but VLOOKUP is shorter to type.
Why does INDEX MATCH return #N/A?
MATCH found no exact match. Check for extra spaces, different data types or a missing 0 in the third argument.
Can INDEX MATCH return multiple results?
A single formula returns one result. Use FILTER in Excel 365 to return all matches.
Does INDEX MATCH work in Google Sheets?
Yes. It works the same way.
What does the 0 in MATCH mean?
It tells MATCH to find an exact match instead of the nearest value.
Need Excel work done for you?
More free tools: All free calculators · Excel automation services · Excel templates · Budget template