INDEX MATCH
=INDEX(A2:A,MATCH(E2,C2:C,0))A compatible option for older files.
PASTE-READY RECIPE
Reverse VLOOKUP in Google Sheets
Look to the left of the match column by using XLOOKUP or INDEX MATCH.
QUICK ANSWER
The formula and where it fits.
Look to the left of the match column by using XLOOKUP or INDEX MATCH. Use =XLOOKUP(E2,C2:C,A2:A,"Not found"), then replace the sample ranges with the columns in your sheet.
XLOOKUP can return a column to the left of the lookup column.
- Put the source data under one header row.
- Paste the formula into an empty result cell.
- Change the ranges and criteria to match your sheet.
- Check the result against one row you can verify manually.
TRY ITEdit the table.
Edit the table.
Watch the result change.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Team | |
| 2 | |||
| 3 | |||
| 4 |
FORMULA OUTPUTLeoRecalculates from the input cells above
VARIATIONS
Use the version that fits your sheet.
Return several columns
=XLOOKUP(E2,C2:C,A2:B,"Not found")Returns name and team together.
Case-sensitive
=INDEX(A2:A,MATCH(TRUE,EXACT(E2,C2:C),0))Treats uppercase and lowercase as different.
COMMON ERRORS
When the result looks wrong.
VLOOKUP cannot look left
Use XLOOKUP or INDEX MATCH instead.
More than one row matches
Use FILTER if you need every match.
QUESTIONS
Answers before you paste.
VLOOKUP cannot look left
Use XLOOKUP or INDEX MATCH instead.
More than one row matches
Use FILTER if you need every match.
MAKE IT YOURS
Adjust this formula →Change the ranges, labels or criteria.
Send this recipe to the SheetSteps assistant and describe how your columns are arranged.