Excel INDEX MATCH — How to Use It (with Examples)

Contents

What is INDEX MATCH?
How INDEX works
How MATCH works
Combining them
5 real examples
Common mistakes
FAQ

What is INDEX MATCH?

INDEX MATCH is not a single formula — it is two formulas working together. INDEX returns a value at a specific position. MATCH finds the position of a value in a list. Nested together, they create the most flexible lookup in Excel.

VLOOKUP can only look right. INDEX MATCH can look in any direction. VLOOKUP breaks when columns are inserted. INDEX MATCH doesn't. This is why Excel power users prefer INDEX MATCH over VLOOKUP for serious work.

How INDEX works

=INDEX(array, row_num, [col_num])

INDEX returns the value at a specific row and column position in a range. Think of it like coordinates — tell it which row and column, and it gives you the value there.

=INDEX(A1:A10, 3) ← returns whatever is in A3

How MATCH works

=MATCH(lookup_value, lookup_array, [match_type])

MATCH finds the position of a value in a list and returns the row number where it appears. Always use 0 as match_type for exact matching.

=MATCH("Coffee", A1:A10, 0) ← returns 3 if Coffee is in A3

MATCH doesn't return the value — it returns the position. That position feeds into INDEX to get the actual value.

Combining INDEX and MATCH

Feed MATCH's result (a position number) directly into INDEX's row_num argument.

=INDEX(B1:B10, MATCH("Coffee", A1:A10, 0)) = INDEX(prices column, position of Coffee in names column)

💡 The key insight

MATCH does the finding. INDEX does the returning. Feed MATCH into INDEX and you can look up any value, in any direction, from any column — including to the left.

5 real examples

Example 1

Basic lookup — find a price

=INDEX(C2:C100, MATCH(F2, A2:A100, 0))

F2 contains the product to search for. INDEX returns the price from column C for the matching row.

Example 2

Look LEFT — return a value to the left of the search column

Product codes in column C, product names in column A. VLOOKUP can't do this. INDEX MATCH can.

=INDEX(A2:A100, MATCH("PRD-003", C2:C100, 0))

Example 3

Find the top performer's name

=INDEX(A2:A20, MATCH(MAX(B2:B20), B2:B20, 0))

MAX finds the highest score. MATCH finds which row it's in. INDEX returns the name from that row.

Example 4

Two-way lookup (row AND column)

=INDEX(B2:F10, MATCH(H2, A2:A10, 0), MATCH(H3, B1:F1, 0))

H2 = row to find, H3 = column to find. Returns the value at the intersection.

Example 5

With IFERROR for clean error handling

=IFERROR(INDEX(C2:C100, MATCH(F2, A2:A100, 0)), "Not found")

Common mistakes

⚠️ Forgetting 0 in MATCH

Always use 0 (or FALSE) as MATCH's third argument for exact matching. Omitting it defaults to 1 (approximate match on sorted data), which gives wrong results on unsorted data.

⚠️ Mismatched array sizes

The lookup_array in MATCH and the array in INDEX must cover the same rows. If MATCH searches A2:A100, INDEX must reference the same row range — e.g. B2:B100 not B1:B100.

⚠️ Using INDEX alone

INDEX on its own requires you to know the row number. In practice you almost always use it with MATCH to find the row dynamically.

FAQ

Should I use INDEX MATCH or XLOOKUP?

If you're on Excel 2019 or Microsoft 365, use XLOOKUP for most lookups — it's simpler. Use INDEX MATCH when you need a two-way lookup (row and column), when working in Excel 2016 or earlier, or when you specifically need MATCH's position value for another formula.

Is INDEX MATCH faster than VLOOKUP?

On very large datasets, INDEX MATCH can be significantly faster because MATCH doesn't have to process the entire table array — only the lookup column. On smaller datasets the difference is negligible.

Can INDEX MATCH return multiple columns?

Yes — use multiple INDEX MATCH formulas, one for each column you want to return. Or in modern Excel, use XLOOKUP with a multi-column return array, which is cleaner.

Practise INDEX MATCH live

ExcelPro has INDEX MATCH exercises across all specialist tracks. Free to start — no download needed.

Try INDEX MATCH exercises →

Related formulas

VLOOKUP XLOOKUP MATCH IFERROR SUMIF IF

INDEX MATCH in Excelexplained simply