INDEX + MATCH in Google Sheets
MATCH finds the position of a value in a single row or column: =MATCH("Ana", A2:A20, 0) returns 3 if Ana is the third name. INDEX returns the value at a given position: =INDEX(C2:C20, 3).
Combined, they make a lookup that doesn't care where the columns are: =INDEX(C2:C20, MATCH(F1, A2:A20, 0)). Unlike VLOOKUP it can look left, and inserting columns won't break it. The 0 in MATCH means exact match.
Syntax
=INDEX(return_range, MATCH(search_key, lookup_range, 0))
Example
=INDEX(C2:C20, MATCH(F1, A2:A20, 0))
Returns column C for the name typed in F1.
Common mistakes
- Leaving out the 0 in MATCH.
- Return and lookup ranges that start on different rows.
- Passing a whole table to MATCH instead of one column.
← VLOOKUP · XLOOKUP → · All lessons