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 29th 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.








{ 10 comments… read them below or add one }
SWEEEET! That is one of the awesomest things ever!! Thank you so much!!!! ^_^
Thanks Colleen. I’m glad it helped you. Your feedback makes it all worthwhile.
I was stuck with “Vlookup to the left” in two days. this is amazing. Thank you so much.
Thanks Huy. Glad to have helped.
Mynda
Hi Mynda, The Best!!!! I have ever seen in my Excel search….
sweet and simple but great concept
Cheers Rajesh. Glad you liked it.
A neat trick… I personally prefer to reorganize the columns in the first place however as it adds less calculational load.
Thanks Scott. Me too, but mainly because it adds less load on my brain
Hi Mynda,
It was very great trick…..I am very keen to learn the Excel, need your suggestions that which book or site is good…
Thanks Parveen! This site is good to learn Excel
We have an Excel Expert training course that you can join. Find out more here:
http://www.myonlinetraininghub.com/excel-expert
Please let me know if you have any questions.
Kind regards,
Mynda.