Quick Answer & Formula
Excel
Sheets
Intermediate
Recommended: =XLOOKUP(val, lookup_col, return_col) | Classic: =INDEX(return_col, MATCH(val, lookup_col, 0)) VLOOKUP cannot natively look to the left. The modern solution is =XLOOKUP(Target, Lookup_Col, Return_Col). In classic Excel, use =INDEX(Return_Col, MATCH(Target, Lookup_Col, 0)).
How to VLOOKUP from Right to Left in Excel (Reverse Lookup)
A major limitation of VLOOKUP is its inability to look up a value in column B and return data from column A. Here are the 3 best ways to perform a left lookup.
Method 1: Modern XLOOKUP (Cleanest & Fastest)
=XLOOKUP(Target_Name, B2:B100, A2:A100)Method 2: INDEX & MATCH (Universal Compatibility)
=INDEX(A2:A100, MATCH(Target_Name, B2:B100, 0))Method 3: The Famous VLOOKUP + CHOOSE Hack
If forced to use the VLOOKUP function name, use CHOOSE({1,2}, ...) to reverse the columns in memory:
=VLOOKUP(Target_Name, CHOOSE({1,2}, B2:B100, A2:A100), 2, FALSE)
?
Frequently Asked Questions
What is the VLOOKUP + CHOOSE trick?
You can trick VLOOKUP into looking left by using CHOOSE to swap column orders in memory: =VLOOKUP(Val, CHOOSE({1,2}, ColB, ColA), 2, FALSE).