My Online Training Hub

Microsoft Office Online Training

Get our newsletter and join over 80,000
people who learn Excel with us every week
You are here: Home / Excel Formulas / Excel VLOOKUP to the Left Using CHOOSE

Excel VLOOKUP to the Left Using CHOOSE

by 132 Comments

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.

Let’s look at our data:

Excel VLOOKUP to the left with the CHOOSE Function

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.

CHOOSE Function

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?

VLOOKUP to the Left

Excel VLOOKUP to the left with the CHOOSE Function

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.

Free 100 Excel Tips & Tricks eBook – Click here to Download

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Comments

    • Hi Prasanth,

      Please post your question and a sample Excel file on our forum so we can see what you mean in the context of Excel.

      Thanks,

      Mynda

      Reply

      • I have data like this:
        Month Salesman Region Product Customers Net Sales Profit / Loss
        Jan-07 Joseph North FastCar 8 1,592 563
        Jan-07 Joseph North RapidZoo 8 1,088 397
        Jan-07 Joseph West SuperGlue 8 1,680 753
        Jan-07 Joseph West FastCar 9 2,133 923
        Jan-07 Joseph West RapidZoo 10 1,610 579
        Jan-07 Joseph Middle SuperGlue 10 1,540 570
        Jan-07 Joseph Middle FastCar 7 1,316 428
        Jan-07 Joseph Middle RapidZoo 7 1,799 709
        Jan-07 Lawrence North SuperGlue 8 1,624 621
        Jan-07 Lawrence North FastCar 6 726 236
        Jan-07 Lawrence North RapidZoo 9 2,277 966
        Jan-07 Lawrence West SuperGlue 6 714 221

        Question:
        Need to find total sales in Jan-08,Feb-08,March-08 for salesman=Lawrence and Region=West using VLOOKUP,INDEX&MATCH functiions??

        Reply

          • Already solved using SUMIFS,SUMPRODUCT,SUM Functions,what i need is i am stuck in using INDEX&MATCH,VLOOKUP.
            Just check my formula to make changes:
            Here columns B=Month C=Salesman,D=Region E=Product
            =INDEX($G$4:$G$1082,MATCH(U4&W4&{“Jan-08″,”Feb-08″,”Mar-08”},($C$4:$C$1082)&($D$4:$D$1082)&($B$4:$B$1082),0))
            =VLOOKUP(U4&W4&{“Jan-08″,”Feb-08″,”Mar-08”},CHOOSE({1,2},$C$4:$C$1082&$D$4:$D$1082&$B$4:$B$1082,$G$4:$G$1082),2,0)

  6. Is it possible to make HLOOKUP() look UP rather than Down?
    The value I am (trying to lookup) is a string.
    My ranges for the CHOOSE() are both on a sheet other than the sheet where I am trying to build the HLOOKUP() formula; the ranges are on Tables!Row# 29 (strings) and Tables!Row# 25 ($ values), respectively.
    I tried the following formula:
    =HLOOKUP(C12,CHOOSE({1,2},Tables!29:29,Tables!25:25),2,0)
    Unfortunately the formula returns #N/A.
    Any help will be greatly appreciated

    Reply

    • Hi Bill,

      On the face of it that formula looks like it should work. Are you certain there is a match? #N/A errors mean no match could be found.

      Persoanlly I prefer the INDEX & MATCH method for lookups that can’t be solved by VLOOKUP or HLOOKUP.

      Alternatively, you can share your workbook and problem on our Excel Forum and we can take a closer look.

      Mynda

      Reply

  7. I prefer INDEX MATCH because it is so flexible. Sometimes I alter it to INDEX MATCH MATCH in order to look up two criteria in order to find the lookup value, and I don’t think you can easily do that with VLOOKUP or HLOOKUP. Well, there are plenty of ways to do it, so everyone’s happy.

    Reply

  8. I have used vlookup by making a duplicate of my lookup column to the right as needed by the formula so that as I added new data from the outside source it could stay in the format required by the input and I just added the extra column each month. But with the combination of vlookup and choose, I may just stop adding the duplicated column and be able to get the same result.

    I do notice for my data, it does not recognize errors like a simple vlookup does – so I will have to wait to add the new month’s vlookup formula until I have data for the month or set up a data condition for putting anything in the cell. Right now my workbooks have the formulas set up for the whole year and populate the results as soon as there is data for the correct period. The “iferror” portion of my vlookups for date/time keeps it from populating with “1/0/00 0:00” when there is no data for the period yet.

    Reply

  9. Mynda,

    A great tip which I haven’t seen posted in any other Excel forum before.

    We understand vlookup so using this formula makes sense. Maybe Index/Match is better but we don’t have time to learn it’s syntax… the boss wants their report ‘like yesterday’!

    Instead of using K:K,E:E could we also use named ranges, to help identify which column is which: K:K “Date” and E:E “Volume”?
    =VLOOKUP(DATE(2011,1,29),CHOOSE({1,2},Date,Volume),2,0)

    PS – I found this link from your – Is Power Query the death of VLOOKUP Excel Newsletter. I signed up a few months ago when I was looking for some dashboard tips and tricks 🙂

    Reply

  10. Mynda,
    Thank you for revealing this trick. Till now, I am either creating another temp column to the right of the search column or use INDEX & MATCH combination. This certainly simplifies the work.
    Can this be used on filtered data as well? Appreciate your expert view.

    Reply

    • Hi Sastry,

      Great to hear you’ll be able to use this tip.

      In terms of using it on a filtered list, it won’t make any difference whether the list is filtered or not, the formula will still work. In other words, the formula ignores filtering.

      Kind regards,

      Mynda

      Reply

  12. For Google-sheets it’s actually easier:
    =VLOOKUP(DATE(2011,1,29),{K:K;E:E},2,0)

    The choose way is the only way I found it to work in excel though

    Reply

  15. Hi Mynda,

    I Can’t resist but to give compliments.

    The way you explain a point is like , as if you give us a, “Sweet Ripe Banana – peeled” and, what do we have to do– ‘Just Gulp it down into our Brains! ‘.

    You make it so easy to “Learn.”

    Great Mam.

    🙂

    Reply

  16. At what point when you are writing the formula do you use CTRL + SHIFT + ENTER to insert those curly brackets?

    Reply

    • Hi Rajan,
      Try this:
      =LOOKUP(2,1/(COUNTIF(A1:A100,A1:A100)>1),ROW(A1:A100))
      It will return the row number of the last duplicate value.
      This version: =LOOKUP(2,1/(COUNTIF(A1:A100,A1:A100)>1),C1:C100) will return the value from column C, corresponding to the last duplicate found on column A.
      Catalin

      Reply

  20. Hi Mynda Tracey,
    Thank you very much for your teachings. I owe you a lot!
    Learning the “Excel VLOOKUP to the left using CHOOSE”, I think instead of the comma to separate the constants inside the curly brackets (as it appeares in your description of the problem and on the download file) we should use a \ (back slash) as it appears in the result of the download file.
    Am I right

    Reply

    • Hi Miguel,

      In my version of Excel we use a comma to separate arguments in a formula. However there are regionalized versions of Excel that use other characters. Yours may well be a back slash instead of a comma, so if that’s what you normally use then go with that.

      For me if I download the file on my computer it will have commas separating the arguments.

      I hope that helps.

      Mynda

      Reply

      • Hi Mynda,

        In fact I’m using a Excel in english but operating in a windows, portuguese version. The commas to separate the arguments are substitutred for semicolons, but to separate the constants in the CHOOSE formula, we use a back slash.

        The formula would be like this:
        =VLOOKUP(DATE(2011;1;29);CHOOSE({1\2};$K$2:$K$207;$E$2:$E$207);2;0)
        Many Thanks,

        Miguel

        Reply

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

    • 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

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

      • 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

        • 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

          • 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

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

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

    • 🙂 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

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

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

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

    Reply

      • 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

        • 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

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

    • 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

      • hi Ronald.

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

        Regards,
        Ajay

        Reply

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

    Thanks.

    Reply

  32. 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!
    https://www.myonlinetraininghub.com is really a good source of learning about MS office tools, even best among all what I explore of now.

    Reply

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

    Reply

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

    • 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,

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

          Cheers.

          CarloE

          Reply

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

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

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

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

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

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

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

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

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

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

    • 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

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

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

    Reply

Trackbacks

YouTube Channel Follow us on Facebook Twitter Follow Google+ RSS Feed or Email

Blog Categories

Resources

Excel Resources

Affiliate Program

Become an Affiliate
Secured by Sucuri Rect
✕
Hang On, Don't Go Just Yet.
Excel Tips & Tricks Book

As a thank you for visiting how would you like a
10% Discount Code to use with any of my courses?

I'll also give you a copy of my eBook packed with
100 Excel Tips & Tricks.

And I'll send you my weekly Excel blog posts.

Enter your email address below to receive all of these.

Regards, Mynda (Excel MVP)

mynda treacy mvp
100% Privacy. We don't spam.
✕
SUBSCRIBE AND LEARN EXCEL
Excel Tips & Tricks Book


Learn great Excel features, tips and tricks each week from my newsletter.

Just enter your email below, and as an extra bonus, I'll send you a copy of my '100 Excel Tips & Tricks eBook' - for nothing!

Thanks,

Mynda

 



100% Privacy. We don't spam.
✕
100 FREE EXCEL TIPS & TRICKS
Excel Tips & Tricks Book


Want to learn more great Excel tips and features?

Enter your email below and I'll send you my weekly blog post.

Plus, by signing up you'll get a copy of my '100 Excel Tips & Tricks eBook' - for nothing!




 

100% Privacy. We don't spam.