Look to the left with INDEX and MATCH
PremiumThe IDs are in column C, to the right of the names. In F2, return the Name for the ID in E2 with INDEX and MATCH.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Department | Employee ID | Employee ID | Name | ||
| 2 | Maria | Sales | E-104 | E-122 | |||
| 3 | Jonas | Finance | E-117 | ||||
| 4 | Sofie | IT | E-122 | ||||
| 5 | Lars | Sales | E-131 | ||||
| 6 | Emma | HR | E-140 | ||||
| 7 | |||||||
| 8 |
Premium lesson
Unlock INDEX and MATCH and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for job interviews
Why this works
VLOOKUP always searches the first column of its table, so it cannot return a name that sits left of the ID. INDEX and MATCH take two independent ranges, so direction does not matter.
Common mistake: Reaching for VLOOKUP. It can't return a column to the left of the one it searches, which is exactly what INDEX and MATCH can do.
The answer
- F2
=INDEX(1A2:A6,2MATCH(E2,C2:C6,0))
In plain words: From 1the names in A2:A6, return the one on 2the row where the ID in E2 sits in C2:C6.