INDEX MATCH Generator - More Powerful Than VLOOKUP

Wizard Mode

A more flexible alternative to VLOOKUP. Returns the value of an element in a table or an array, selected by the row and column number indexes.

Configuration

Visual Helper

#
A
B
C
D
1
Item 1
$100
2
ID-123
Item 2
$200
3
Item 3
$300
4
Item 4
$400

Result Function

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

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.

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