INDEX MATCH: The Power User's Lookup Formula

INDEX MATCH is the combination that power users swear by. It's more flexible than VLOOKUP, works in any direction, and is available in every version of Excel. If you learn one advanced lookup pattern, make it this one.

How INDEX MATCH Works

The combination uses two functions together:

• MATCH finds the position of a value in a range and returns a row number
• INDEX returns the value at a specific position in a range

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

MATCH finds where your lookup value sits. INDEX uses that position to grab the value you want. Simple.

INDEX MATCH vs VLOOKUP

Why use INDEX MATCH over VLOOKUP?

• Look in any direction — return column can be to the left of the lookup column
• Column insertions don't break it — you reference ranges, not column numbers
• Faster on large datasets — MATCH only searches one column, not an entire table
• Multiple criteria — combine with arrays for multi-condition lookups

Common Patterns

Basic lookup (replace VLOOKUP):

=INDEX(C:C, MATCH(A2, B:B, 0))

Right-to-left lookup:

=INDEX(A:A, MATCH(D2, C:C, 0))

Two-criteria lookup (Ctrl+Shift+Enter in older Excel):

=INDEX(D:D, MATCH(1, (A:A=G1)*(B:B=G2), 0))

Return entire row:

=INDEX(A2:F100, MATCH(lookup, A2:A100, 0), 0)

When to Use XLOOKUP Instead

If you have Excel 365 or 2021+, XLOOKUP is often simpler for basic lookups. But INDEX MATCH still wins for:

• Multi-criteria lookups
• Returning values from multiple columns at once
• Dynamic row and column references
• Backward compatibility with older Excel versions