Back to Topics

INDEX MATCH

Advanced
15 min

What is INDEX MATCH?

INDEX MATCH is a powerful combination that overcomes VLOOKUP limitations by looking left, right, up, or down to find matching values.

Key Components:

  • Can look up values from any column
  • Faster than VLOOKUP on large datasets
  • More flexible and powerful
  • No column index number to maintain

Why it matters

Left Lookups

Find values left of lookup column

Performance

Faster on large spreadsheets

Flexibility

Horizontal or vertical lookups

Dynamic Ranges

Works with changing column positions

Key Concepts

INDEX

Returns value at position

Example: INDEX(A:A, 5)...

MATCH

Finds position of value

Example: MATCH("A", A:A, 0)...

Combine

INDEX(range, MATCH())

Example: INDEX(B:B, MATCH(A2, A:A, 0))...

0 for exact

Use 0 in MATCH

Example: MATCH(value, range, 0)...

How to use

1

Start with =INDEX(

Begin the INDEX function

2

Select return range

Column with your answer

3

Add MATCH(

Start the MATCH function

4

Select lookup value

What to find

5

Select lookup range

Where to search

6

Add ,0) and close

Use 0 for exact match, double close parentheses

Example

Goal: Find employee name by ID (left lookup)
=INDEX(A:A, MATCH(D2, B:B, 0))
Result: Returns name even though Name is left of ID

Pro Tips

  • •Use 0 for exact: Always use 0 in MATCH
  • •Faster than VLOOKUP: Better performance
  • •Column insert safe: No column index to update

Practice

Use INDEX MATCH to find product price where price is left of product name
PreviousAll Topics