Excel INDEX and MATCH — look left, a flexible VLOOKUP

MATCH finds which row a value is in, and INDEX returns the value from another column in that row. Together they search in any direction and work in every Excel version.

In other languages: İNDİS (Türkçe), ИНДЕКС (Русский); KAÇINCI (Türkçe), ПОИСКПОЗ (Русский)

Syntax

INDEX(array, MATCH(lookup_value, lookup_array, 0))

Arguments

array: INDEX: the column to return from.
lookup_value: MATCH: the value to find.
lookup_array: MATCH: the column to search.
match_type: MATCH: 0 for an exact match.

Example

The code is in the rightmost column; find the product name for the code in E2 (P-103).

ABC

1ProductPriceCode

2Laptop1200P-101

3Monitor350P-102

4Printer280P-103

Type the formula in F2

The formula in your Excel

English Excel=INDEX(A2:A4,MATCH(E2,C2:C4,0))
Türkçe Excel=İNDİS(A2:A4;KAÇINCI(E2;C2:C4;0))
Русский Excel=ИНДЕКС(A2:A4;ПОИСКПОЗ(E2;C2:C4;0))

For P-103 the result is "Printer" — VLOOKUP cannot do this because the name is left of the code.

✨ Have the AI explain this formula

Useful tips

Do not forget 0 as MATCH's last argument.
Inserting columns does not break the formula (unlike VLOOKUP's column number).

Common errors

#N/A — MATCH did not find the value (spaces, text vs number).
A wrong result — the INDEX and MATCH ranges do not start on the same row.