INDEX/MATCH to the left
Return a column that sits before the one you search — which VLOOKUP cannot do.
Task:
The staff list has the employee ID in column A and the email in column C. You have emails from a survey and need the IDs. In F2:F4 return the ID for each email in E2:E4.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of INDEX MATCH
0/4The same formula in the other shapes it takes at work.
- INDEX-MATCH Combination
Use INDEX and MATCH functions together for flexible lookups.
CoreAdvanced - INDEX/MATCH to the left
Return a column that sits before the one you search — which VLOOKUP cannot do.
Advanced - INDEX/MATCH with MAX: who is top?
Find the highest figure, then the name beside it.
Advanced - INDEX/MATCH with an approximate MATCH
MATCH type 1 finds the band; INDEX returns its label from any column.
Advanced
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Name | Survey email | ID | ||
| 2 | E-104 | Kim Lowe | k.lowe@corp.com | r.dias@corp.com | ||
| 3 | E-117 | Rui Dias | r.dias@corp.com | k.lowe@corp.com | ||
| 4 | E-122 | Omar Said | o.said@corp.com | t.nash@corp.com | ||
| 5 | E-130 | Tess Nash | t.nash@corp.com |
What this exercise teachesMay contain the answer
INDEX/MATCH splits a lookup into two independent parts — where is it, and what do I want from that row — so the return column can be anywhere, including to the left. Restructuring the table to suit VLOOKUP is the workaround people use when they do not know this.