INDEX MATCH: The Ultimate VLOOKUP Alternative

The INDEX MATCH combination is widely considered the most flexible lookup method in Excel and Google Sheets. It overcomes VLOOKUP's biggest limitations: it can look left, doesn't break when columns are inserted, and handles large datasets more efficiently.

How INDEX MATCH Works Together

INDEX MATCH is actually two functions working in tandem: MATCH finds the row number where your lookup value appears, and INDEX returns the value from that row in your target column. Together they achieve what VLOOKUP does — but with no column order restrictions.

INDEX MATCH vs VLOOKUP vs XLOOKUP

FeatureINDEX MATCHVLOOKUPXLOOKUP
Look left✅ Yes❌ No✅ Yes
Insert-safe✅ Yes❌ No✅ Yes
Handle not found⚠️ IFERROR⚠️ IFERROR✅ Built-in
Backward compatible✅ All versions✅ All versions❌ Excel 2021+

Advanced: INDEX MATCH with Multiple Criteria

You can match on multiple columns by concatenating criteria within the MATCH function using an array formula:

=INDEX(C:C, MATCH(1, (A:A=E2)*(B:B=F2), 0))

In Excel, press Ctrl+Shift+Enter for array formulas. In Google Sheets, wrap with ARRAYFORMULA().

Best Practice: INDEX MATCH is the go-to choice for shared workbooks that must work across Excel 2010-2019 and Google Sheets. If you only use Excel 365 or Google Sheets, XLOOKUP offers cleaner syntax with built-in error handling.

Common Errors & Fixes

  • INDEX MATCH returns #N/A or wrong value

    Causes:
    • Lookup range and return range have different heights (rows).
    • MATCH type wrong: use 0 for exact match; -1/1 for sorted lookup.
    • Data type mismatch between lookup value and lookup range.
    Fixes:
    • Use same-sized single-column ranges for lookup_range and return_range.
    • Use 0 for exact match in most cases.
    • Normalize types with TRIM, VALUE, or TEXT.

Frequently Asked Questions

What does INDEX MATCH do?

INDEX returns a value from a range by position; MATCH finds the position of a value. Combined, they look up a value and return from another column—and can look to the left, unlike VLOOKUP.

Why use INDEX MATCH instead of VLOOKUP?

INDEX MATCH can look left, is not broken when you insert columns, and often performs better on large data. It is more flexible than VLOOKUP.

How do I use INDEX MATCH with multiple criteria?

Use MATCH with an array formula or helper column that concatenates criteria. In Excel 365 you can use XLOOKUP with multiple conditions more easily.

What is the MATCH type (0, -1, 1)?

0 = exact match. -1 = find smallest value >= lookup (ascending). 1 = find largest value <= lookup (descending). Use 0 for most lookups.

Why is INDEX MATCH returning #N/A?

MATCH returns #N/A when the lookup value is not found. Check for data type mismatch (number vs text), extra spaces, or use IFERROR to handle not found.

Related Formulas

VLOOKUPXLOOKUPIFERRORSUMIF

Want to become an Excel Pro?

Stop searching for formulas. Master Excel in 30 days with this top-rated course.

Learn More