INDEX + MATCH in Excel — Crash Course

INDEX+MATCH is a powerful lookup pattern used for flexible, dynamic lookups in Excel.
You’ll often hear it said as “INDEX+MATCH”, but in practice it’s really MATCH inside INDEX.

You can use XLOOKUP instead in modern Excel. They achieve similar results with different tradeoffs.

When to Use INDEX + MATCH

Use INDEX+MATCH when:

• The lookup column is not the first column
• You want a dynamic, non-breaking lookup
• You want more control than VLOOKUP allows
• You’re working in older Excel versions without XLOOKUP

MATCH — Finds the Position (Row Number)

MATCH returns the position of a value inside a single column (or row).

Syntax

MATCH(lookup_value, lookup_array, match_type)

• lookup_value → what you’re searching for
• lookup_array → must be one column or one row
• match_type → usually 0 for exact match

Important Rules

• Output is 1-indexed
• The row number depends on the size of the range

Example

If value is in C10:

• MATCH("Apples", C1:C20, 0) → 10
• MATCH("Apples", C10:C20, 0) → 1

Same cell, different output because the range changed.

INDEX — Returns the Value at a Position

INDEX returns a value from a table based on row (and optionally column).

Syntax

INDEX(array, row_num, [column_num])

Instead of hardcoding row_num, you place the MATCH function here.

Basic INDEX + MATCH Example

Example Table

Goal: Find the Price of “Carrots”.

=INDEX(B2:B4, MATCH("Carrots", A2:A4, 0))

What happens:

1. MATCH finds the row where “Carrots” exists
2. INDEX returns the price from that row

The lookup and return columns are different — this is the power.

Critical Rule: Ranges Must Align

The MATCH range and INDEX range must cover the same rows.

If MATCH returns row 3, INDEX must also have a 3rd row to pull from.

Advanced: Lookup with Two Criteria (Row + Column)

If you need to find a value based on two parameters (e.g., Cookie Type and Country):

• Use MATCH twice
• Use INDEX with both results

=INDEX(Table1,
       MATCH(E7, Table1[Cookie Type], 0),
       MATCH(E8, Table1[#Headers], 0))

One MATCH finds the row. The other finds the column.

Advanced MATCH: Multiple Criteria with &

You can combine fields to match on multiple conditions.

=MATCH(E7&E8,
       Table1[Cookie Type]&Table1[Country],
       0)

• Lookup value: E7&E8
• Lookup array: combined columns with &

INDEX + MATCH vs XLOOKUP

Both solve the same problem.

• INDEX+MATCH → more manual control, works everywhere
• XLOOKUP → simpler syntax, modern Excel

Key Takeaways

• MATCH finds the position
• INDEX returns the value at that position
• MATCH must use a single row/column
• INDEX and MATCH ranges must align
• You can use two MATCH functions for two criteria
• You can combine fields with & for multi-condition matching