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:

Excel VLOOKUP and CHOOSE Functions

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.

Share This

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

Leave a Comment

Current day month ye@r *

{ 89 comments… read them below or add one }

Colleen March 30, 2011 at 3:30 am

SWEEEET! That is one of the awesomest things ever!! Thank you so much!!!! ^_^

Reply

Mynda March 30, 2011 at 12:40 pm

Thanks Colleen. I’m glad it helped you. Your feedback makes it all worthwhile.

Reply

Huy May 20, 2011 at 12:54 am

I was stuck with “Vlookup to the left” in two days. this is amazing. Thank you so much. :-)

Reply

Mynda May 25, 2011 at 9:35 am

Thanks Huy. Glad to have helped.

Mynda

Reply

Rajesh Peter October 23, 2011 at 3:19 pm

Hi Mynda, The Best!!!! I have ever seen in my Excel search….
sweet and simple but great concept :)

Reply

Mynda Treacy October 23, 2011 at 9:42 pm

Cheers Rajesh. Glad you liked it.

Reply

Scott Gall February 13, 2012 at 11:00 pm

A neat trick… I personally prefer to reorganize the columns in the first place however as it adds less calculational load.

Reply

Mynda Treacy February 14, 2012 at 3:15 pm

Thanks Scott. Me too, but mainly because it adds less load on my brain :)

Reply

Parveen Saroha April 30, 2012 at 4:36 am

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…

Reply

Mynda Treacy April 30, 2012 at 10:30 pm

Thanks Parveen! This site is good to learn Excel ;)

We have an Excel training course that you can join. Find out more here:

http://www.myonlinetraininghub.com/free-microsoft-office-online-training/microsoft-office-online-training-courses/excel-2007-syllabus

Please let me know if you have any questions.

Kind regards,

Mynda.

Reply

Dan June 12, 2012 at 2:51 am

This formula is perfect and helped so much. Thank you.

Reply

Mynda Treacy June 12, 2012 at 2:23 pm

Cheers, Dan :)

Reply

Ron Kaminker June 26, 2012 at 9:10 am

Always use match and index instead of vlookup.

With Vlookup, if you add or delete a column, the entire spreadsheet can blow up.

Is there a way to look in a list of cells to see if a cell matches (where the cells are NOT contiguous: something like Match($a1,{a11,b12,c13,d14},0)

Reply

Mynda Treacy June 26, 2012 at 4:22 pm

Hi Ron,

Are you just trying to see if there is a match or locate the cell containing the match? i.e. would a TRUE or FALSE answer do? Also, are the values numeric or text?

Cheers,

Mynda.

Reply

krishnan June 29, 2012 at 4:42 pm

wan to know more abt excel

Reply

Barcelona August 3, 2012 at 12:54 am

Excellent job and very good tutorials.
The “English translation” is very helpful!

I’ll include a link to this site in my blog to keep the trace and avoid missing time with other useless tutorials I found.

Reply

Mynda Treacy August 3, 2012 at 8:44 pm

Cheers, Barcelona!

Reply

john tang August 10, 2012 at 8:35 pm

Mynda,

This is really an eye opener. Thanks.

John.

Reply

Mynda Treacy August 10, 2012 at 8:38 pm

Cheers, John. Glad you like it :)

Reply

Christopher Bloome August 21, 2012 at 6:16 am

Hi Mynda, This is great, but for some reason, when I try this in practice, it shows me the data that is two cells below what I’m actually looking for. Any ideas where I might be messing up?

Thanks!

CB

Reply

Ashish Dimri August 24, 2012 at 2:12 pm

Wonderful, this makes a lot of tasks easier!!

Reply

Mynda Treacy August 24, 2012 at 8:05 pm

Thanks, Ashish :)

Reply

Jason August 31, 2012 at 11:46 pm

I was struggling with trying to complete this exact same kind of function…. I ended up “googling” my problem and ended up at this page… I read through your instructions, applied it to my spreadsheet and it worked! Thank you so much for your help!!!

Reply

Mynda Treacy September 2, 2012 at 5:12 pm

:) Cheers, Jason. Glad we could help.

Kind regards,

Mynda.

Reply

birbal sharma October 1, 2012 at 1:29 am

hi mynda ,
mynda please send me some access logic for do something extra in my office

Birbal Sharma

Reply

Mynda Treacy October 1, 2012 at 10:24 am

Hi Birbal,

I’m sorry, I don’t have any training on Access.

Kind regards,

Mynda.

Reply

birbal sharma October 1, 2012 at 1:33 am

mynda its my luck i got you for my Excell logic clear

Reply

Mynda Treacy October 1, 2012 at 10:25 am

Cheers, Birbal :)

Reply

Khurram Ali October 8, 2012 at 8:25 am

Dear Mynda,

Thanks a lot for keep teaching us a number of useful and creative formulas and functions in Excel.

Please send me link to your some files/examples about using using data tables, esp. with reference to dynamic/interactive ranges/graphs.

So kind of you,

Khurram Ali

Reply

Mynda Treacy October 8, 2012 at 12:10 pm

Hi Khurram Ali,

You can find an index of Excel formulas and techniques here, including Tables.

Sorry, I don’t have any tutorials on the blog on dynamic/interactive ranges/graphs at this time. They are covered in my dashboard course though.

Kind regards,

Mynda.

Reply

Ras October 20, 2012 at 2:39 am

Much better explained than an example I found in Chandoo.org on looking up to the left. Thank you.

Reply

Mynda Treacy October 20, 2012 at 7:12 am

Wow, thanks, Ras :)

Reply

steve page October 31, 2012 at 9:07 am

The choose formula you just showed me pulling data out from the column to the left makes much more sense to me than the index/match method. it had been awhile since i used the index/match method and when i went to use it i could remember what columns went where….lol.
the choose method you just showed is 10x easier to remember. I use this function more than most folks and it is a lifesave. Thank you so much for sharing.

Reply

Mynda Treacy October 31, 2012 at 10:02 am

:) Thanks, Steve. I like VLOOKUP with CHOOSE too. :)

Reply

Kel November 12, 2012 at 10:33 am

Mynda thanks heaps this will save me hours of frustration.

Reply

Mynda Treacy November 12, 2012 at 12:48 pm

Great to hear, Kel :)

Reply

Maxime Manuel November 21, 2012 at 1:14 am

It’s awesome! I was just looking for something like this.

I bookmarked this website.

Thank you! :-)

Reply

Mynda Treacy November 21, 2012 at 7:33 am

:) You’re welcome, Maxime

Reply

bob mcglynn November 24, 2012 at 12:30 am

I believe that this site is one of the most open and comprehensive repository of Excel useful info i have come across in months.

B

Reply

Mynda Treacy November 24, 2012 at 9:24 am

Wow, thanks Bob :)

Reply

Roger Cousins December 13, 2012 at 7:26 am

Awesome… my new favourite function!
I can’t tell you how many times I’ve struggled with this. I even went so far as to create a separate worksheet with {column E, Column C} so that I could lookup the values in C. Never More.

In what chapter of what book in what universe is Choose() explained as well as this?

Reply

Mynda Treacy December 13, 2012 at 8:28 pm

:) Thanks for your kind words, Roger.

Reply

Garth December 20, 2012 at 8:12 pm

This is really useful.

Thanks!

Reply

Mynda Treacy December 21, 2012 at 10:46 am

Glad I could help :)

Reply

Steve December 22, 2012 at 7:39 am

FYI – you can “HLOOKUP to the up” by using the same trick, but with one important change. {1,2} becomes {1;2}. (Change the comma to a semicolon!)

Reply

Mynda Treacy December 23, 2012 at 8:27 pm

Hi Steve,

Love it. Thanks for sharing.

Kind regards,

Mynda.

Reply

brian July 29, 2013 at 4:10 am

Thanks – very helpful and very clear

Reply

Mynda Treacy July 29, 2013 at 1:36 pm

Thanks, Brian :) Glad you liked it.

Reply

Mustafa January 19, 2013 at 11:54 pm

I usually like to link or import the excel list to ms access and do all these tricks even easir. And still likes to know more about excel.
Still your site is great. Thanks.

Reply

Mynda Treacy January 20, 2013 at 9:27 am

Cheers, Mustafa :)

Reply

Abid Jan January 31, 2013 at 9:00 pm

Hey,

I am looking Vlookup from left, Its amazing:)

Thanks,
Abid Jan

Reply

Mynda Treacy January 31, 2013 at 9:51 pm

Cheers, Abid :) Glad you liked it.

Reply

J. Andrew Steinbach February 5, 2013 at 5:16 am

Hello,

This information is exactly what i needed.

However i am struggling to apply it to my vlookup that looks up on different worksheet, any insight would be greatly appreciated.

Current Formula looks like:
=VLOOKUP(B5,’MAIN REPORT’!D:E,2,0)

-It searches what is in cell B5 on worksheet MAIN REPORT in column D and returns the corresponding data from column E. However the data in column E is really to the left, column C, and i must manually copy column C to column E.

-Thanks Again

Reply

Carlo Estopia February 5, 2013 at 12:13 pm

Hi JAndrew,

I simulated your problem:

In MAIN REPORT ColA to ColE
ColC – “DataFromC”
ColD – “LookupME”

IN your Formula-Sheet

B5 – “LookupME”
E5 – is the formula below.

 =VLOOKUP(B5,CHOOSE({1,2},'MAIN REPORT'!D:D,'MAIN REPORT'!C:C,2),2,0) 

Read More on VLOOKUP with CHOOSE

Cheers.

CarloE

Reply

J. Andrew Steinbach February 6, 2013 at 1:39 am

It worked!!!! i love you! Thank you so much! i will refer co-workers here.

Reply

Carlo Estopia February 6, 2013 at 9:39 am

J. Andrew,

On behalf of Mynda — actually, she wrote it and I learned it from her–
I say you’re very much welcome.

Cheers.

CarloE

Reply

Suzie Hardy February 21, 2013 at 5:47 pm

Thanks for this, CHOOSE works much simpler in my head than index and match, this is a very quick shortcut – love it.

Reply

Mynda Treacy February 22, 2013 at 10:13 am

You’re welcome, Suzie :)

Reply

Manjeet April 10, 2013 at 2:02 am

It’s awesome. Before this I was aware of trick to look-up value toward left is Index-match combination. This is far easier than that one. :) :) :)
Thanks!
http://www.myonlinetraininghub.com is really a good source of learning about MS office tools, even best among all what I explore of now.

Reply

Mynda Treacy April 10, 2013 at 10:55 am

Thanks, Manjeet :)

Reply

Robert Wernke April 24, 2013 at 7:00 am

Excellent!!! Helped allot!!!!

Reply

Mynda Treacy April 24, 2013 at 8:35 am

You’re welcome, Robert :)

Reply

Nishal April 25, 2013 at 7:24 pm

Hi

Can we use Sumif with Look up, as you aave used choose function

Thanks
Nishal

Reply

Mynda Treacy April 25, 2013 at 7:56 pm

Maybe. In what context exactly?

Reply

Sergiu April 28, 2013 at 4:33 am

Very useful courses in your website.
VLOOKUP with CHOOSE combination – good trick instead of MATCH INDEX combination.

Thanks.

Reply

Mynda Treacy April 28, 2013 at 8:12 pm

Thanks, Sergiu :)

Reply

Ronald May 7, 2013 at 11:12 am

Hi Mynda,

Great! Is there a way for Vlookup or Match to return the nearest higher value to a lookup_value? Value returned is always the greatest value which is <= to the lookup_value where data is sorted in ascending order for the Vlookup function and in descending order for the Match function. The goal is actually to get the next lower and next higher value of a lookup_value.

Thanks,
Ron

Reply

Mynda Treacy May 7, 2013 at 8:45 pm

Hi Ronald,

How about this:

=VLOOKUP(A1+1,B1:C10,2)

i.e. add 1 to your lookup value to make it find the next higher value. Take 1 away to find the next lower value.

Kind regards,

Mynda.

Reply

Ajay Jangral August 8, 2013 at 6:16 pm

hi Ronald.

U can try “large or small” function to get the kth higher or lower value.

Regards,
Ajay

Reply

Sharon May 10, 2013 at 10:21 pm

Awsome! So easy to use. I didn’t experiment with your workbook, just copied the code & changed to match my data. 10/10

Reply

Mynda Treacy May 12, 2013 at 8:28 pm

Cheers, Sharon :)

Reply

Tefo May 21, 2013 at 12:44 am

Hi Myanda

thanks for all the helpful tutorials.

just one thing, get you give anothere example of nesting vlookup with choose function that involves something else either than a date.

many thanks.

Reply

Carlo Estopia May 29, 2013 at 11:08 am

Hi Tefo,

Please clarify some more because I think it really doesn’t matter whether it’s a date or something else, this
Vlookup with choose function will work.

Cheers,

CarloE

Reply

swetha June 12, 2013 at 3:55 am

really excellent!!! very well explained about the choose function. many thanks :)

Reply

Mynda Treacy June 12, 2013 at 7:33 am

Thank you, Swetha :)

Reply

Ashutosh Bhatnagar July 1, 2013 at 10:03 pm

Hi,
I want to change the dependent drop downlist as I change the drop down list of master for eg If I change the state Than it should show the store list of that state only in the dropdown. please help how can i do the same in excel.
Thanks
Ashutosh
+919650197720

Reply

Mynda Treacy July 1, 2013 at 10:30 pm

Hi Ashutosh,

You can read tutorials on Dependent Data Validation here, and a different approach here.

Kind regards,

Mynda.

Reply

Krishna R M July 3, 2013 at 1:14 am

Amazing that was an awesome trick…!!! This helps particularly when we have to apply multiple vlookups with various columns and where you may have to look both left and right for different values…!!!

After reading your website I feel we can force excel to do anything for us…!!

The NodeXL add-in you suggested for drawing Network Chart is Just amazing….!!

Reply

Mynda Treacy July 3, 2013 at 2:48 pm

Thanks, Krishna :)

Glad you like it.

Reply

rachel August 1, 2013 at 2:17 pm

this is so amazing! i’ll never go to Index/Match again (which, after a couple of years, i STILL can’t get the hang of). using CHOOSE is so much easier and intuitive! thanks for this!

Reply

Mynda Treacy August 1, 2013 at 6:57 pm

:) Glad you liked it, Rachel. Although if you can force yourself to get your head around INDEX & MATCH it’ll be worth your while.

Reply

yaser October 13, 2013 at 8:14 am

Dear mynda
u r great !
i am learning a lot from ur tutorials

i have a problem ,could u assist me ?

if i want to look up for a specific name and return the relevant value
ex.
if i have two columns,
one of them is containing the brand name of the drug,
and the other is containing the active ingredient of the same drug,

like: scientific name is ranidine,
the related brand name is zantac
but the problem there are other specifications written in the columns of active ingredient like :ranidine 150 mg ,
so if i want to look for ranidine (only) vlookup does not work bec the look up area dosent contain (ranidine only),

so,can advise how can i solve this problem ?

Regards,
yaser

Reply

Mynda Treacy October 13, 2013 at 9:33 pm

Hi Yaser,

You can use a VLOOKUP formula with wildcards. Click here to see how.

Kind regards,

Mynda.

Reply

yaser October 14, 2013 at 1:18 am

thx ,mynda

u r super !!!!
really amzing and clever method which u gave me !!!

however i hv other problems:

1-if i have another column (third one )can use the same vlookup formula (column index no= 3) bec when i tryed ,does not work?
2-the other problem ,if the returned value are more than one item,
can I create a formula to bring all the items which contain the same/ (looking up word)?
hope my Q. are clear.

regards,
yaser
hop

Reply

Mynda Treacy October 14, 2013 at 9:17 am

Hi Yaser,

I’m not sure why it won’t return column index number 3. I presume it’s because VLOOKUP can’t find a match, but without knowing the error I can’t be sure.

You can return multiple matches with this formula.

Kind regards,

Mynda.

Reply

yaser October 14, 2013 at 10:01 am

thx,mynda,
however i want to return the whole(mutiple) results by looking up of (part of the whole lookup field ,like the previous vlookup formula that u gave me ).

pls advise.

regards,
yaser

Bruce November 17, 2013 at 8:21 am

Very helpful and creative! Is there a way to write a formula that will return the left column results for different dates without having to painstakingly enter them in the Date formula? If you convert the dates to serial numbers like you suggested that might be my answer

Reply

Catalin Bombea November 17, 2013 at 6:14 pm

Hi Bruce,
You can use a worksheet cell for VLOOKUP search criteria, like: =VLOOKUP(N8,CHOOSE({1,2},$K$2:$K$207,$E$2:$E$207),2,0) , in cell N8 just enter the date to search. This way you can avoid reediting the formula. If you have a column with search dates, you can copy down this formula to find the results for each date. Make sure that cell N8 is formatted as date; if it’s formatted as text, you can convert to date by using DATEVALUE(N8) in VLOOKUP formula instead of N8 reference.
Another good thing to know: Vlookup is returning only the first match found, this means that if you have duplicates in the search column (like multiple rows for same day) , the formula will return only the value corresponding to the first match found!
Hope it helps,
Cheers,
Catalin

Reply

Mynda Treacy October 14, 2013 at 1:13 pm

Hi Yaser,

It’s very difficult to picture what you want. Can you please send me an example Excel file via the help desk which specific instructions on what you want and where.

This will help me to help you.

Thanks,

Mynda.

Reply

Previous post:

Next post: