Vlookup to the left: 3 ways to lookup left in excel

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.

Vlookup to the left
Vlookup to left

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.

  1. Vlookup with IF array formula
  2. Vlookup with CHOOSE array formula
  3. 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)
vlookup to the left

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.

Subscribe to our website to know more about Vlookup.

5 thoughts on “Vlookup to the left: 3 ways to lookup left in excel”

  1. 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

    Reply

Leave a Comment