FormulaFixer / Formula reference / INDEX/MATCH

INDEX/MATCH formula

Two formulas used together to look up a value — more flexible than VLOOKUP because it can look to the left, doesn't break when columns are inserted, and doesn't require the lookup column to be first.

Syntax

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

MATCH finds the position of lookup_value within lookup_range; INDEX then returns whatever sits at that position inside return_range. Because those two ranges are independent, return_range can be to the left, right, or on a different sheet entirely from lookup_range — the limitation that trips up VLOOKUP. The trailing 0 in MATCH means "exact match," which is almost always what you want here, same as VLOOKUP's FALSE.

Examples

=INDEX(B2:B50,MATCH(A2,C2:C50,0))

Finds A2's position in C2:C50, then returns the value at that same position from B2:B50.

=INDEX(Sheet2!A:A,MATCH(A2,Sheet2!B:B,0))

Looks up a value in column B but returns a result from column A — to the left, which VLOOKUP can't do directly.

=INDEX(C2:C50,MATCH(1,(A2:A50="Widget")*(B2:B50="East"),0))

A multi-criteria version matching on two columns at once — enter with Ctrl+Shift+Enter in older versions of Excel.

Paste your own INDEX/MATCH formula and get a plain-English explanation, or describe what you need and let FormulaFixer write it for you.

Explain an INDEX/MATCH formula →

More formulas