Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to do a reverse (left) lookup in Excel and Google Sheets
Topic:VLOOKUPExcel & Google Sheets
=INDEX(A2:A5,MATCH(D2,B2:B5,0))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Identify the column containing the lookup value (e.g. column B) and the column with the result to the left (e.g. column A).
- 2Put the value to search for in a cell (e.g. D2).
- 3Enter =INDEX(A2:A5,MATCH(D2,B2:B5,0)).
- 4MATCH finds the row position of D2 in column B; INDEX returns the value at that row in column A.
Tips
- INDEX MATCH is generally preferred over VLOOKUP even for right-side lookups, as it does not break when you insert or delete columns.
- In Excel 365 and Google Sheets, XLOOKUP is even simpler for reverse lookups: =XLOOKUP(D2,B2:B5,A2:A5).
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Can VLOOKUP do a left lookup at all?
Only with a workaround using CHOOSE to reorder the columns virtually: =VLOOKUP(D2,CHOOSE({1,2},B2:B5,A2:A5),2,0). INDEX MATCH is cleaner.
What if there are duplicate values in the lookup column?
MATCH returns the first match. For the last match, use LOOKUP(2,1/(B2:B5=D2),A2:A5).
More on VLOOKUP
See all →Reference
VLOOKUP
Look up a value in the first column of a range and return a value from another column in the same row.
How-toHow to VLOOKUP from another sheet
Pull a value from a different tab with VLOOKUP by prefixing the range with the sheet name. Works in Excel and Google Sheets.
Error fixFix #N/A
Fix #N/A errors in VLOOKUP in Excel and Google Sheets — covers missing matches, extra spaces, data-type mismatches, and range mistakes.
How-toHow to VLOOKUP with multiple criteria
Look up a value that matches two or more conditions using INDEX/MATCH with joined keys. Works in Excel 365 and Google Sheets.
Error fixFix Wrong value
VLOOKUP returns an incorrect value in Excel and Google Sheets — fix wrong column index, approximate match mode, unsorted data, and duplicate lookup keys.
How-toHow to fix VLOOKUP returning #N/A
VLOOKUP shows #N/A when it can’t find a match. Here are the real causes — exact-match, spaces, text-vs-number — and how to fix each.