Excel How To VLOOKUP Left (3 ways)

If you’ve used the VLOOKUP function before, you know that the lookup column is expected to be in the leftmost column within the range. Meaning, it’s going to look for a value in the leftmost column within the range and once it finds its match, it will shoot to the right to return the related value. This means that the lookup column has to be positioned to the left of the return value:

But what happens if that’s not the case? What happens if the lookup column is positioned to the right of the return column:

Well … long story short, VLOOKUP isn’t designed to look left. So, what are we supposed to do … manually reposition the columns? No! We have several options.

Video

Details

In this post, we’re going to look at three different ways to do a lookup that returns a value that lies to the left of the lookup column:

• XLOOKUP
• INDEX/MATCH
• CHOOSE/CHOOSECOLS

Let’s head to Excel.

XLOOKUP

Now, the first option is to use XLOOKUP instead of VLOOKUP. This is a great option if the XLOOKUP function is available in your version of Excel. To find out if it is, simply head to any cell and type “=XL”. If you see XLOOKUP pop-up, you have access to this function and this is the one I would use instead of VLOOKUP.

Here is our lookup table (Table1):

We are trying to lookup the DeptNum (second column) and return the corresponding Department name (first column).

So, assuming the lookup value is located in B6, the lookup column is in a table named Table1, we’d enter the following formula:

=XLOOKUP(B6, Table1[DeptNum], Table1[Department])

• The first arugment B6 is the lookup value
• The next argument Table1[DeptNum] is the lookup column
• The next argument Table1[Department] is the return column

As you can see, we reference the lookup and return columns independently, so the column order does not matter.

But what if we don’t have XLOOKUP in our version of Excel? Well, that takes us to the next option.

INDEX/MATCH

Now, let’s take a look at using INDEX and MATCH instead of VLOOKUP. This is a great option when our version of Excel doesn’t have XLOOKUP. The formula looks like this:

=INDEX(Table1[Department], MATCH(B6, Table1[DeptNum], 0))

• The first argument Table1[Department] is the return column
• B6 is the lookup value
• Table1[DeptNum] is the lookup column
• 0 is for exact match

So, that’s the second option – using INDEX and MATCH instead of VLOOKUP.

But is there a way to actually do an actual VLOOKUP left? The answer is yes. There is. Let’s go to the next example.

CHOOSE/CHOOSECOLS

Alright, so we actually want to use the actual VLOOKUP function in this case. So, there’s a couple of helper functions that enable us to do this. The first is CHOOSECOLS. If your version of Excel has CHOOSECOLS, I’d go with that option. If not, we have the CHOOSE function as another option. Let’s see what these two helper functions actually do.

First, let’s do CHOOSECOLS, which basically allows us to pick and choose not only which columns from a range we wish to return, but also the column order.

For example, if I wanted to return the first and second column from Table1, in that order, I’d write this:

=CHOOSECOLS(Table1, 1, 2)

If I wanted to return the second column and then the first column, I’d use this instead:

=CHOOSECOLS(Table1, 2, 1)

It enables us to look at an entire range and then pick and choose which columns we want to return and in which order.

So, we can simply use CHOOSECOLS as the range argument in VLOOKUP, like this:

=VLOOKUP(B6, CHOOSECOLS(Table1, 2, 1), 2, 0)

• The first argument is the lookup value B6
• The CHOOSECOLS function returns the second and then first columns of Table1
• 2 returns the value in the second column
• 0 for exact match

So, that’s one of the helper functions that we can use when we really want to use VLOOKUP to do this. The other helper is CHOOSE. The syntax for using the CHOOSE function as the lookup argument to VLOOKUP is this:

=VLOOKUP(B6,CHOOSE({1,2},Table1[DeptNum],Table2[Department]),2,0)

The CHOOSE function essentially reverses the order of the columns.

Conclusion

Well, those are a few ways to write a formula that works when the lookup column is positioned to the right of the return column. These options eliminate the need to manually change the column order. If you have any other tips or tricks, or ways to improve the formulas presented, please share by posting a comment below … thanks!

File