INDEX MATCH Explained: The Ultimate Lookup Formula

INDEX MATCH is the Swiss Army knife of lookup formulas. It works in any Excel version, looks in any direction, and outperforms VLOOKUP.

Understanding Each Function

INDEX: Returns a Value at Position

=INDEX(array, row_number, [column_number])

=INDEX(A1:A100, 5)
Returns the value in the 5th row of A1:A100

=INDEX(A1:C100, 5, 2)
Returns value at row 5, column 2 (B5)

MATCH: Finds the Position

=MATCH(lookup_value, lookup_array, [match_type])

=MATCH("Apple", A1:A100, 0)
Returns the position of "Apple" in the range
0 = exact match (always use this)

If "Apple" is in A8, returns 8

Combining INDEX + MATCH

Basic Lookup (Replaces VLOOKUP)

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

Example:
=INDEX(C2:C100, MATCH("John", A2:A100, 0))

Find "John" in column A, return corresponding value from column C

Why This is Better Than VLOOKUP

• ✅ Look left: Return column can be LEFT of lookup column
• ✅ Insert columns: Won't break if you add columns
• ✅ Faster: Searches only lookup column, not entire table
• ✅ Clearer: Explicitly states which columns to use

Real-World Examples

Salary Lookup

Data:
A2:A20 = Employee Names
D2:D20 = Salaries

=INDEX($D$2:$D$20, MATCH(E2, $A$2:$A$20, 0))

Looks up employee in E2, returns their salary
$ makes references absolute for copying down

Look Up to the Left

Data:
B2:B50 = Product Codes (lookup column)
A2:A50 = Product Names (return column - left of lookup!)

=INDEX($A$2:$A$50, MATCH("PROD-123", $B$2:$B$50, 0))

VLOOKUP can't do this!

Two-Way Lookup (Row AND Column)

Data table:
      Jan  Feb  Mar
Alice  100  150  200
Bob    120  180  220
Carol  110  160  210

=INDEX($B$2:$D$4,
    MATCH("Bob", $A$2:$A$4, 0),
    MATCH("Feb", $B$1:$D$1, 0)
)

Returns: 180

First MATCH finds Bob's row (2)
Second MATCH finds Feb column (2)
INDEX returns value at row 2, column 2

Advanced Techniques

Multiple Criteria Lookup

Find row where First Name = "John" AND Last Name = "Smith"

=INDEX($D$2:$D$100,
    MATCH(1,
        ($A$2:$A$100=F2) * ($B$2:$B$100=G2),
    0)
)

Enter as array formula: Ctrl + Shift + Enter (older Excel)
Excel 365: Just press Enter

The * creates AND logic (1 only when both match)

Lookup Closest Match Below

Tax brackets, shipping tiers, volume discounts:

Sorted data:
A2:A10 = Income thresholds (ascending order)
B2:B10 = Tax rates

=INDEX($B$2:$B$10, MATCH(C2, $A$2:$A$10, 1))

Match type 1 = closest value less than or equal
REQUIRES sorted data in ascending order!

Return Multiple Columns

=INDEX($A$2:$E$100, MATCH("John", $A$2:$A$100, 0), 3)

Returns column 3 (C) for John's row

Change the last number (3) to return different columns:
1 = column A, 2 = column B, etc.

Error Handling

Basic error handling:
=IFERROR(
    INDEX($C$2:$C$100, MATCH(E2, $A$2:$A$100, 0)),
    "Not Found"
)

Shows "Not Found" instead of #N/A error

Check if exists first:
=IF(COUNTIF($A$2:$A$100, E2) > 0,
    INDEX($C$2:$C$100, MATCH(E2, $A$2:$A$100, 0)),
    "Not in list"
)

Performance Comparison

Feature	VLOOKUP	INDEX MATCH	XLOOKUP
Direction	Right only	Any direction	Any direction
Column changes	Breaks	Resilient	Resilient
Speed	Slower	Faster	Fastest
Compatibility	All versions	All versions	365 & 2021+
Two-way lookup	No	Yes	No

Common Mistakes

• ❌ Different range sizes: MATCH range must have same # of rows as INDEX range
• ❌ Relative references: Forgot $ signs when copying formula
• ❌ Wrong match type: Use 0 for exact match (99% of cases)
• ❌ Unsorted data with match type 1/-1: Gives wrong results