What it does
INDEX MATCH combines MATCH to find a position and INDEX to return a value from that position.
Syntax or pattern
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))✅
5 practical examples
1
Find a price by product
Return the price for the product name in A2.
=INDEX(Products[Price], MATCH(A2, Products[Product], 0))MATCH finds the row; INDEX returns the price.
2
Look left in a table
Find a product name using a product code in a right-side column.
=INDEX(Products[Product], MATCH(D2, Products[Code], 0))INDEX MATCH can return values from columns to the left.
3
Two-way lookup
Find the intersection of a product row and month column.
=INDEX(SalesData, MATCH(H2, Products, 0), MATCH(H3, Months, 0))Useful for matrix-style reports.
4
Use with IFERROR
Return a friendly message when the match is missing.
=IFERROR(INDEX(Prices, MATCH(A2, Products, 0)), "Not found")This avoids showing #N/A in a report.
5
Match by exact ID
Use IDs to avoid duplicate-name problems.
=INDEX(Staff[Manager], MATCH(E2, Staff[Employee ID], 0))Exact ID matching is more reliable than name matching.
Common mistakes to avoid
- Using MATCH without the 0 exact-match argument.
- Using ranges with different lengths.
- Selecting the wrong return range in INDEX.
Related Excel examples
FAQ
Is INDEX MATCH better than VLOOKUP?
It is more flexible, especially for left lookups and dynamic table structures.