Excel + Google Sheets · INDEX + MATCH
The lookup that works in every Excel
MATCH finds the row, INDEX returns the value. Works in any Excel version.
The formula
=INDEX(B2:B4, MATCH(D2, A2:A4, 0))What each part means
B2:B4What to returnD2What you look forA2:A4, 0Where, exact match
Example
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ID | Client | Find | Result | |
| 2 | C-07 | Laura | C-12 | ||
| 3 | C-12 | João | |||
| 4 | C-03 | Priya |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ID | Client | Find | Result | |
| 2 | C-07 | Laura | C-12 | João | |
| 3 | C-12 | João | |||
| 4 | C-03 | Priya |
Step by step
- Click cell E2.
- Type the formula:
=INDEX(B2:B4, MATCH(D2, A2:A4, 0)) - Press Enter.
- Drag the small square in the bottom-right corner of the cell down to copy it to the other rows.
Where it works
- Every version of Excel
- Google Sheets
- Excel for the web (free)
The same formula in other languages
- English INDEX + MATCH
- Español INDICE + COINCIDIR
- Português ÍNDICE + CORRESP