Lookup formula
INDEX MATCH in Excel and Google Sheets
A flexible lookup that works in every Excel version and can look left.
Syntax
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))- return_range
- The column with the values you want back.
- lookup_value
- What you are looking for.
- lookup_range
- The column to search in.
- 0
- Exact match.
Example
| Row | A | B |
|---|---|---|
| 1 | Name | City |
| 2 | Stockholm | SE |
| 3 | Oslo | NO |
| 4 | Helsinki | FI |
| 5 | ||
| 6 | Code | NO |
B7 =INDEX(A2:A4,MATCH(B6,B2:B4,0))
Result: Oslo
MATCH finds the row of "NO" in column B, INDEX returns the city from column A on that row.
Ready-to-copy formulas
Look up to the left
=INDEX(A:A,MATCH(E2,C:C,0))Two-way lookup (row and column)
=INDEX(B2:M10,MATCH(P2,A2:A10,0),MATCH(Q2,B1:M1,0))
Tips
- Faster and sturdier than VLOOKUP on large sheets.
- Both ranges must be the same size.
Need the whole spreadsheet?
Start from a free template with every formula already in place. Opening a template uses no credits.