The formula
Excel & Google SheetsExcelGoogle Sheets
=INDEX(A2:A5,MATCH(B7,C2:C5,0))In E2 → Cara Lee
Excel 365 / Google SheetsExcelGoogle Sheets
=XLOOKUP(B7,C2:C5,A2:A5)In E3 → Cara Lee
✓ Tested. All 2 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Dept | Employee ID | ||
| 2 | Ana Diaz | Sales | E-104 | Cara Lee | |
| 3 | Ben Cho | Ops | E-221 | Cara Lee | |
| 4 | Cara Lee | Finance | E-310 | ||
| 5 | Dev Rao | Sales | E-415 | ||
| 6 | |||||
| 7 | ID | E-310 |
How it works
- A2:A5
- The column to return (can be anywhere, left or right).
- MATCH(B7,C2:C5,0)
- Position of the ID in the ID column, exact match.
- XLOOKUP(B7,C2:C5,A2:A5)
- Search C, return A. Lookup and return ranges are separate, so direction doesn't matter.
Common errors and fixes
Why does the formula return #N/A?
ID not found. Check for trailing spaces or IDs stored as numbers vs text.
Wrong person returned?
The two ranges start on different rows. Use A2:A5 with C2:C5, not C1:C4.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.