INDEX MATCH in Google Sheets
INDEX MATCH is the classic flexible lookup: MATCH finds the row, INDEX returns the value from any column you point it at.
=INDEX(B2:B100, MATCH(E2, A2:A100, 0))
MATCH finds the row of E2 in A2:A100; INDEX returns that row's value from B2:B100. The 0 forces an exact match.
How it works
MATCH(key, range, 0) returns the position of the key within a single-column (or single-row) range. INDEX(range, position) returns the value at that position. Chaining them means the return column is independent of the lookup column, so it works right-to-left too.
Variations
Two-way lookup (row and column)
=INDEX(B2:F100, MATCH(H2, A2:A100, 0), MATCH(H3, B1:F1, 0))
First MATCH picks the row, second picks the column.
With a not-found fallback
=IFERROR(INDEX(B2:B100, MATCH(E2, A2:A100, 0)), "Not found")
MATCH returns #N/A when the key is missing.
Examples
| Scenario | Formula |
|---|---|
| Return name for a given ID (ID is right of name) | =INDEX(A2:A100, MATCH(E2, B2:B100, 0)) |
| Grade at the intersection of student + subject | =INDEX(B2:F100, MATCH("Ana",A2:A100,0), MATCH("Math",B1:F1,0)) |
FAQ
INDEX MATCH or XLOOKUP?
XLOOKUP is shorter for a plain lookup. INDEX MATCH is still handy for two-way (row + column) lookups and in older files.
Why the 0 in MATCH?
0 means exact match. Leaving it out (or 1) does an approximate match and needs the range sorted ascending.