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