How to Use INDEX MATCH in Excel: Simple Guide

Use INDEX combined with MATCH in Excel to look up a value by finding its position with MATCH and returning the corresponding value with INDEX. This combo is more flexible than VLOOKUP because it can look left and handle dynamic ranges.

Syntax

The INDEX MATCH formula combines two functions:

• INDEX(array, row_num, [column_num]): Returns the value at a specific row and optional column in a range.
• MATCH(lookup_value, lookup_array, [match_type]): Finds the position of a value in a range.

When combined, MATCH finds the row number, and INDEX returns the value at that position.

excel

INDEX(array, MATCH(lookup_value, lookup_array, 0))

Example

This example shows how to find the price of an item using INDEX MATCH. Suppose you have a list of items in column A and prices in column B.

Formula to find the price of "Apple":

excel

A1:A4: Item
Apple
Banana
Cherry
Date

B1:B4: Price
1.2
0.5
2.0
3.0

Formula in C1:
=INDEX(B1:B4, MATCH("Apple", A1:A4, 0))

Output

1.2

Common Pitfalls

Common mistakes when using INDEX MATCH include:

• Using a non-exact match type (not 0) in MATCH, which can give wrong results.
• Mixing up the order of arguments in INDEX and MATCH.
• Not locking ranges with $ when copying formulas, causing errors.

Always use 0 as the third argument in MATCH for exact matches.

excel

Wrong: =INDEX(B1:B4, MATCH("Apple", A1:A4))  
Right: =INDEX(B1:B4, MATCH("Apple", A1:A4, 0))

Quick Reference

Tips for using INDEX MATCH:

• Use exact match: Always set MATCH third argument to 0.
• Flexible lookup: Can look left or right unlike VLOOKUP.
• Lock ranges: Use $ to fix ranges when copying formulas.
• Combine with IFERROR: Wrap formula with IFERROR to handle missing values gracefully.

Key Takeaways

INDEX MATCH is a powerful alternative to VLOOKUP for flexible lookups.

Use MATCH with 0 as the match type for exact matches.

INDEX returns the value at the position MATCH finds.

Lock your ranges with $ to avoid errors when copying formulas.

Combine with IFERROR to handle missing lookup values cleanly.