LOOKUPS

INDEX + MATCH

Build a flexible lookup using a position finder and a return range.

After this lessonUnderstand the lookup pattern that works in older Excel versions too.

How it works

MATCH returns the position of a lookup value. INDEX returns the value at a given position in a range. Combined, they create a flexible exact-match lookup.

The 0 in MATCH requests an exact match, which is normally the right choice for codes and IDs.

REAL WORK

Look up prices or attributes when XLOOKUP is unavailable or when you want explicit control over lookup position.

SYNTAX

The pattern

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

Example

=INDEX(C2:C6,MATCH("SKU-103",A2:A6,0))

TRY IT

Test the formula

The sample sheet is local to this page. Change the formula and see the result immediately.

Result

QUICK CHECK

What does MATCH return?

Progress is stored only in this browser. No account required.