INDEX + MATCH Lookup

Excel & Google Sheets

How it works

MATCH(E2,A2:A10,0) finds which row of A2:A10 contains the value in E2 — the 0 means exact match. INDEX(B2:B10, …) then returns the value at that same row position in B2:B10. Because the two ranges are independent, the return column can be anywhere relative to the lookup column, and inserting columns between them changes nothing.

MATCH(E2,A2:A10,0)

Finds the position of E2 within A2:A10. Returns a row number like 3.

0

Exact match. Always use 0 unless your data is sorted and you want nearest-match.

INDEX(B2:B10, …)

Returns the value at that position from the return column.

When to use it

Use INDEX + MATCH when you need compatibility with older Excel versions, or a two-way lookup by pairing two MATCHes — one for the row, one for the column.

Common mistakes

• ✕Omitting the 0 in MATCH.

  ✓MATCH defaults to approximate match, which returns wrong positions on unsorted data. Always specify 0 for exact match.

• ✕INDEX and MATCH ranges start on different rows.

  ✓If MATCH searches A2:A10, INDEX must return from B2:B10 — not B1:B10 — or every result is shifted by one row.