Excel VLOOKUP to the Left Using CHOOSE

A little while ago I showed you how to do a lookup to the left using the INDEX and MATCH functions.

In this Excel tutorial I’m going to show you how you can do a lookup to the left with a VLOOKUP formula together with the CHOOSE function as an alternative.

First of all let’s look at our data:

In this example we want to look up the Volume in column E for the date 29/01/2011 in column K. As we know, a VLOOKUP cannot go left, but with the help of the CHOOSE function we can trick it into going left.

First of all let’s understand how the CHOOSE function works:

This is the syntax in Excel:

=CHOOSE(index_num, value1, value2, value3…..up to 254 values)

The syntax is not very useful as usual! To translate it into English:

=CHOOSE(value number 3 where, value 1 = A, value 2 = B, value 3 = C)

The result is C

Now we can get creative by specifying more than one index number with the help of curly brackets { }, and instead of specifying the values (like we did above with A, B and C) we can refer to a range of cells like this:

=CHOOSE({1,2},$K$2:$K$207,$E$2:$E$207)

In English this formula reads:

= ({column 1 is K , and column 2 is E)

Effectively switching the positions of column E and K so that the VLOOKUP will think column K is to the left of column E. Clever, huh?

Now, on its own, like the example above, CHOOSE is not much use but when you use it in a VLOOKUP it enables us to trick Excel into returning the value to the left of our lookup column.

Our formula to look up date 29^th January 2011 in column K and return the value in column E (column number 2) is:

=VLOOKUP(DATE(2011,1,29),CHOOSE({1,2},$K$2:$K$207,$E$2:$E$207),2,0)

Translated:

=VLOOKUP(find 29/01/2011 in column K and return the value in column E)

Result 7,222,425

NOTE: when you want to reference a date in a formula you need to tell Excel it is a date using the DATE function, alternatively you can use the date’s serial value. However, I find the DATE function more intuitive and easier to follow when I revisit a formula later on. Alternatively you could reference another cell that contains the date in the correct date format.

TIP: We can make this formula a little easier to use by changing the cell range references to full column references. This will work in this instance because there is no other data in our columns other than that which is in our table.

With full column references our formula looks like this:

=VLOOKUP(DATE(2011,1,29),CHOOSE({1,2},K:K,E:E),2,0)

Download the workbook and play around with the formula to test your understanding.

In some ways I think using the CHOOSE function to trick your VLOOKUP to look left is easier than the INDEX and MATCH functions , especially if you’re more familiar with VLOOKUP.

What do you think? Do you have a preference or do you just rearrange your columns so you never have to lookup to the left? Let me know in the comments below.

Share This

Tweet

Please share this or leave a comment and I'll make sure you get a personal reply.

Tagged as: lookup, vlookup