Vlookup is the most widely used function in Excel. As we all know Vlookup searches for the given value in a table and brings the value of that column or the column which is in the right side. That means by default Vlookup can look right side in the table. However with the combination of other functions we can make Vlookup to look from right to left. In this article let us understand how to use Vlookup to the left side search.
How to use Vlookup to the left search
There are three methods to do this. Two methods are with vlookup formula and one more is combination of INDEX and MATCH which is alternate to VLOOKUP FUNCTION.
- Vlookup with IF array formula
- Vlookup with CHOOSE array formula
- INDEX MATCH to lookup left
1. Vlookup with IF array formula
To lookup left, we can use IF function while defining table range in vlookup formula. Detail about how to enter if formula is explained below.
=VLOOKUP(F7,IF({1,0},D:D,B:B),2,0)
2. Vlookup with CHOOSE array formula
Similar to above formula, instead of IF function, we can use CHOOSE function while defining the table range in Vlookup. CHOOSE with Vlookup can do much more than this, i will explain that in our next post. The details about how to use this formula is explained below.
=VLOOKUP(F15,CHOOSE({1,2},D:D,B:B),2,0)
3. INDEX MATCH to lookup left
The above two formulas will make search Vlookup to the left, but when the data in the table is large, these above formulas will take lot of time and slow down your Excel. To overcome this issue, alternate formula is combination of INDEX and MATCH to perform same task easily.
INDEX function return the value of an element in a table based on row number and column number. MATCH function searches for the given value in a range and return the position for matching value.
=INDEX(B:B,MATCH(F23,D:D,0),1)
The combination of both these functions will be an alternative for Vlookup function. INDEX MATCH is a powerful formula which will do everything which Vlookup dose and much more.
These are the three methods to vlookup to the left in excel. You can download the file with formula in below link.
Hi ,
I came across this formula on for VLOOKUP to the left but didn’t understand it
VLOOKUP(IF({1,0},B$5:E$16,2,FALSE)
I found this video too
https://www.youtube.com/watch?v=6JluR45VJl4
This video skips the IF function altogether and just puts the both the Lookup column and destination column in braces
In both these cases the destination column is the second one in braces and 2 is the column index number
Can you help me understand how it works?
Of course i understand that INDEX / MATCH and XLOOKUP is a better option but i just wanted to understand the Concept of why IF function works without a true and false entry in this case.
My intention is to clear any misconceptions and learn new concepts since you are extremely good at breaking down the formula and explaining it