What is INDEX MATCH?

INDEX MATCH is a powerful combination of two Excel functions that performs lookups. It's more flexible than VLOOKUP and works in any direction.

The Two Functions

INDEX

=INDEX(array, row_num, [col_num])

Returns value at specific position in array

Example:
=INDEX(A1:A10, 5)
Returns value in 5th position of A1:A10

MATCH

=MATCH(lookup_value, lookup_array, [match_type])

Returns position of value in array

Example:
=MATCH("Apple", A1:A10, 0)
Returns row number where "Apple" is found
(0 = exact match)

Combining INDEX and MATCH

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

MATCH finds the position
INDEX returns the value at that position

Basic Example

Sample Data

     A          B
1    Product    Price
2    Apple      1.50
3    Orange     2.00
4    Banana     1.25

Look up price of "Orange":

INDEX MATCH Solution

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

How it works:
1. MATCH("Orange", A2:A4, 0) → Returns 2 (Orange is in position 2)
2. INDEX(B2:B4, 2) → Returns value at position 2 = 2.00

Result: 2.00

Compared to VLOOKUP

VLOOKUP version:
=VLOOKUP("Orange", A2:B4, 2, FALSE)

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

Both return: 2.00

Why INDEX MATCH is Better

1. Lookup Left (VLOOKUP Can't)

Data:
     A          B
1    Price      Product
2    1.50       Apple
3    2.00       Orange

Find product with price 2.00:

INDEX MATCH: ✅ Works!
=INDEX(B2:B3, MATCH(2.00, A2:A3, 0))

VLOOKUP: ❌ Can't look left!

2. No Column Counting

VLOOKUP requires column number:
=VLOOKUP("Orange", A2:D4, 3, FALSE)
If you insert column, formula breaks!

INDEX MATCH uses direct reference:
=INDEX(C2:C4, MATCH("Orange", A2:A4, 0))
Insert columns anywhere - still works!

3. Faster with Large Data

INDEX MATCH only searches lookup column
VLOOKUP searches entire table array
Big difference with large datasets!

Common Patterns

Pattern 1: Basic Lookup

=INDEX(return_column, MATCH(lookup_value, lookup_column, 0))

Example:
=INDEX($C$2:$C$100, MATCH(E2, $A$2:$A$100, 0))

Pattern 2: Two-Way Lookup

=INDEX(data_range, MATCH(row_value, row_headers, 0), MATCH(col_value, col_headers, 0))

Example:
     A       B      C      D
1           Q1     Q2     Q3
2  Product1 100    150    200
3  Product2 120    180    240

Find Product2 Q2 sales:
=INDEX(B2:D3, MATCH("Product2", A2:A3, 0), MATCH("Q2", B1:D1, 0))
Result: 180

Pattern 3: Lookup with Criteria

Multiple conditions using array formula:

=INDEX(return_col, MATCH(1, (crit1_col=val1)*(crit2_col=val2), 0))

Must enter with Ctrl+Shift+Enter (older Excel)
Excel 365: Works automatically

Real-World Examples

Example 1: Employee Lookup

Data:
A: Employee ID | B: Name | C: Department | D: Salary

Find salary for employee "E12345":
=INDEX($D$2:$D$100, MATCH("E12345", $A$2:$A$100, 0))

Find name for highest salary:
=INDEX($B$2:$B$100, MATCH(MAX($D$2:$D$100), $D$2:$D$100, 0))

Example 2: Dynamic Column Lookup

Data table with months as columns:
     A        B    C    D    E
1  Product   Jan  Feb  Mar  Apr
2  Widget    100  120  130  140

User selects month in F1 (dropdown)
Formula in F2:
=INDEX($B$2:$E$2, MATCH($F$1, $B$1:$E$1, 0))

Result: Returns sales for selected month

Example 3: Closest Match

Tax brackets:
A: Income | B: Tax Rate
0          0%
10000      10%
40000      22%
85000      24%

Find tax rate for $50,000:
=INDEX(B2:B5, MATCH(50000, A2:A5, 1))

Note: match_type = 1 (finds largest value ≤ lookup)
Must sort ascending!
Result: 22%

Advanced Techniques

Multiple Criteria

Data:
A: Product | B: Region | C: Sales

Find sales for "Widget" in "East":
=INDEX($C$2:$C$100, MATCH(1, ($A$2:$A$100="Widget")*($B$2:$B$100="East"), 0))

Ctrl+Shift+Enter for array formula

Return Multiple Columns

Return entire row:
=INDEX($A$2:$D$100, MATCH(lookup, $A$2:$A$100, 0), column_offset)

column_offset:
1 = column A
2 = column B
3 = column C
etc.

Case-Sensitive Lookup

=INDEX(return_col, MATCH(TRUE, EXACT(lookup_val, lookup_col), 0))

EXACT is case-sensitive
Regular MATCH is not

Error Handling

Handle #N/A Errors

=IFERROR(INDEX(..., MATCH(...)), "Not Found")

Or:
=IFNA(INDEX(..., MATCH(...)), "Not Found")

Check if Value Exists

=IF(ISNA(MATCH(lookup, range, 0)), "Not Found", "Found")

INDEX MATCH vs Alternatives

Function	Pros	Cons
VLOOKUP	Simple, familiar	Only right, column counting, slower
XLOOKUP	Modern, flexible, easy	Only Excel 365
INDEX MATCH	Flexible, fast, any Excel version	More complex syntax

Common Mistakes

Mistake 1: Wrong Match Type

WRONG: =MATCH("Apple", A1:A10)
Defaults to 1 (approximate), usually wrong!

RIGHT: =MATCH("Apple", A1:A10, 0)
0 = exact match

Mistake 2: Mismatched Ranges

WRONG: =INDEX(B2:B10, MATCH(val, A1:A10, 0))
Ranges different sizes!

RIGHT: =INDEX(B2:B10, MATCH(val, A2:A10, 0))
Same size ranges

Mistake 3: Forgetting $ in References

WRONG: =INDEX(B2:B10, MATCH(E2, A2:A10, 0))
When copied, ranges shift!

RIGHT: =INDEX($B$2:$B$10, MATCH(E2, $A$2:$A$10, 0))
Lock with $ signs

Performance Tips

• Use specific ranges: Not entire columns (A:A)
• Lock references with $: Prevents recalculation
• Use match_type = 1 when possible (faster, but requires sorted data)
• Consider XLOOKUP if you have Excel 365

Quick Reference

Basic:
=INDEX(return_range, MATCH(lookup_val, lookup_range, 0))

Two-way:
=INDEX(data, MATCH(row_val, row_range, 0), MATCH(col_val, col_range, 0))

With error handling:
=IFERROR(INDEX(..., MATCH(...)), "Not found")

Multiple criteria:
=INDEX(return, MATCH(1, (crit1)*(crit2), 0))

Match types:
0 = Exact match (most common)
1 = Less than or equal (requires sorted ascending)
-1 = Greater than or equal (requires sorted descending)

Best Practices

• Always use 0 for exact match unless you need approximate
• Lock all ranges with $ when copying formulas
• Test with small data first before applying to large datasets
• Use named ranges for better readability
• Add IFERROR for production formulas
• Consider XLOOKUP for new workbooks (if Excel 365)