ExcelLab
ExercisesGuidesLeaderboardPricing
Guides
/

Lookup

INDEX MATCH

How to use INDEX MATCH in Excel (a better VLOOKUP alternative)

MATCH finds the position of a value, and INDEX returns the value at that position. Together they can look up in any direction.

Syntax

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
  • return_range — The range of values to return.
  • lookup_value — The value to find.
  • lookup_range — The range to search in.
  • 0 — Exact match for MATCH.

Example

ABC
1CodeProductPrice
2P01Notebook8000
3P02Pen3000
4P03Pencil2000

Formula:

=INDEX(C2:C4,MATCH("P02",A2:A4,0))

→ result: 3000

MATCH

=MATCH("P02",A2:A4,0) → 2. Returns the position of the value within the range.

INDEX

=INDEX(C2:C4,2) → 3000. Returns the value at the given position in the range.

Why INDEX+MATCH is better

The return column can be to the left of the lookup column, the formula doesn't break when columns are inserted, and it runs faster on large tables.

Frequently asked questions

Exact match. 1 or -1 are used for approximate match in a sorted list.
Yes: =INDEX(B2:E10,MATCH(H1,A2:A10,0),MATCH(H2,B1:E1,0)) — by row and by column.