Look up a value with INDEX and MATCH

Why it's built this way

• →MATCH does the finding — it returns a row NUMBER, not the value itself — and INDEX uses that number to pull the actual result out of return_range; splitting the job this way is what lets the two ranges point anywhere, in either direction.
• →The third argument to MATCH, 0, forces an exact match. Leave it off and MATCH defaults to approximate matching against a sorted list, which returns a confidently wrong row on data that isn't sorted.
• →Because return_range and lookup_range are independent, the return column can sit to the LEFT of the lookup column — a layout VLOOKUP can never do without rearranging the sheet.

The fine print

• →Runs in every version of Excel back to the 1990s and in Google Sheets — the reason it's still worth knowing even with XLOOKUP available.
• →lookup_range and return_range must have the same number of rows, or the formula returns a #REF! or #VALUE! error.
• →A missing value returns #N/A, same as VLOOKUP; wrap the whole thing in IFERROR if a blank or a message is preferred instead.

Part by part

=INDEX(

Returns a value from a range by position. It knows nothing about what it is looking up — MATCH supplies the position.

{{return_range}}

The column holding the answer. Because it is named separately, it can sit to the left of the lookup column, which VLOOKUP cannot do.

MATCH(

Finds the position of a value within a range and returns that number. Together the two do the job VLOOKUP does with a fixed column offset.

{{lookup_range}}

The column to search. It must be the same height as the return range, or the position MATCH finds will point at the wrong row.

, 0)

The match type, and the most important argument here. Zero means exact match; omitting it defaults to approximate and requires sorted data.

Common mistakes

• =INDEX(B:B, MATCH(D2, A:A))

  Omitting the third argument makes MATCH default to approximate matching, which assumes the lookup column is sorted ascending. On unsorted data it returns a plausible wrong answer rather than an error.

  =INDEX(B:B, MATCH(D2, A:A, 0))
• =INDEX(B2:B100, MATCH(D2, A2:A500, 0))

  Mismatched range heights. MATCH returns a position within a 499-row range and INDEX applies it to a 99-row one, so the result is either the wrong row or #REF!.

• =VLOOKUP(D2, A:B, 2, FALSE)

  Works until someone inserts a column inside the range, at which point the number 2 still points at the second column of the range and that is now different data. The formula does not error, it just returns the wrong field.

Questions