The ultimate VLOOKUP trick – Multi-condition Lookup

Posted on October 28th, 2014 in Learn Excel - 134 comments

This is a guest post by Sohail Anwar.

Let’s not bore you with an intro. You are about to learn a VLOOKUP trick that Lucifer himself would not want you to know. It’s so absurdly powerful that it was developed in a lab and had to be tested on Rocky’s arch nemesis Ivan Drago.

VLOOKUP Trick for Multi-condition lookups

Presenting the Multiple criteria VLOOKUP!

…boring…pass, we’ve seen it.

Oh, have you? Not like this you haven’t. This will change the way you work with Excel.

Let me start with an easy example. Here’s some data and we would love to know what Bb and Dd is.

Example data - Multi criteria lookup in Excel

Easy. Let’s put a helper column in that concatenates the two inputs and do a basic VLOOKUP.

Multi-condition lookup using helper columns

Puh-lease. How boring.

Bye Bye Helper Column, it was nice while it lasted.

With a dash of CHOOSE and sprinkling of Array formulas, we’re about to change the game:

=VLOOKUP($E2,CHOOSE({1,2},$A$2:$A$7&$B$2:$B$7,$C$2:$C$7),2,0) and press Ctrl + Shift + Enter

Multi-conditional VLOOKUP with CHOOSE - Explained

Without getting into too many details, using the Array creates a makeshift virtual helper column. You don’t have to understand Array formulas to make them work for you. I will lay out the simple structure that you can replicate

VLOOKUP(lookup value, CHOOSE({1,2,...N},Column1 & Column 2 &…& Column N, Result Column),2,0)

Where the lookup value is either something pre-concatenated (like Bb or Dd above) or you are using multiple criteria that you concatenate when entering the lookup value. The CHOOSE structure is easy. Always {1,2} then concatenate (with &) as many columns as you want (that the lookup values will need to look in) and the VLOOKUP’s column number is always 2. Let’s explore another example:

Multi-condition vlookup - another example

Let’s say we want to look up the Savings Produced for a Director of Grade D who started in 2014. That’s 3 lookup criteria. Let’s follow the structure.

=VLOOKUP(A13&A14&A15,CHOOSE({1,2},A2:A10&B2:B10&C2:C10,D2:D10),2,0) and press Ctrl + Shift + Enter

The two key things to note is that our lookup value is a concatenation of the criteria, in this case I have put the criteria in A13, A14 and A15 (hence A13&A14&A15 is our lookup value). Secondly, in the CHOOSE formula, the ranges in the middle part (A2:A10&B2:B10&C2:C10) have to be concatenated in the same order that the lookup value was concatenated. So we concatenated:

Start Year & Grade & Role

In both the lookup value and lookup columns within the CHOOSE.

I stumbled on this many years ago at work and it is the easiest way to do multiple criteria lookups. Play around and add more criteria…but that’s just the beginning!

When I get that feeling, it’s like Textual Healing

So how can we take this concept and make it even more useful?

First, let me share my story of pain and anguish.

Often when dealing with volumes of text data I make numerous helper columns to deal with the multitude of ways I am presented with names. Anyone who’s reconciled HR data to Finance data for example can appreciate that pain. Finance write their names First Name (column 1) Surname (column 2), then HR provide a spread with Last Name, Surname (column 1), then all of a sudden the Project team join in the fun with First Name, Surname (column 1)! Arrghh!

So I am now left to deal with this chaos via numerous text formulas involving SEARCH, LEFT, RIGHT, MID, LEN and MYSANITY (okay perhaps that last one is my own UDF, my volatile UDF). So, maybe it’s not that bad, but when you’ve been doing it for as long as I have, it gets tedious and you begin to search for efficiency. So, one day like the rebellious closing scene from Dead Poet’s society, I stood on my desk and declared ‘Oh Captain, My Captain’ as I refused to create another ‘helper’ column.

No more inconsistent data - using multi-condition lookups to handle inconsistent data

After my colleagues talked me down from the table and reassured me (“There there Sohail, I don’t mind inserting new columns for you occasionally”…”Sure you don’t John, sure you don’t”), I went about finding a less ‘helpful’ way. Would you believe, our new friend the multiple criteria lookup was the answer.

You see, not only can our criteria be cell references but also extra characters! Let’s say we have First Name(Column A), Surname (Column B) and Unique Reference (Column C). Someone gives us a spreadsheet with the names in either a First Name + Surname or Surname, First Name format. We can look this up by including the extra characters in our lookup columns within the CHOOSE.

Handling inconsistent data with multi-condition array lookup formulas

Look closely at the middle of the CHOOSE since that’s where the magic is. Download the workbook to see the example in action.

Multi-condition array lookup formula in action

We have pretty much instructed the two columns we are looking up to join up in a specific way. First we want them to join up with a space in between. Then the second formula has asked them to join up Surname, comma and space in between, then finally the First Name. So as far as Excel is concerned we have created two virtual helper columns that look like this:

How virtual helper columns work - Multi-condition lookup formula

This makes it straightforward for us to look up John Johnson or Johnson, John in them.

There are virtually no bounds to how you can use this Multiple Criteria VLOOKUP. It made my life tremendously easy and I’m sure it makes yours easier too. Do me a favor and let me know in the comments some of the crazy ways you are applying it.

And then if you haven’t already grabbed a copy of Chandoo’s VLOOKUP book I cannot recommend it enough as the ultimate resource in VLOOKUP mastery

Download Example Workbook

Click here to download the example workbook prepared by Sohail. Play with it to learn more.

Added by Chandoo

Thank you Sohail

Thank you Sohail for writing this very useful, incredibly fun tutorial. I am sure our readers will enjoy it as much as I do. Thanks.

If you like this, please say thanks to Sohail.

Related discussion on Multi-conditional lookups

As you can guess, this is not the first time we talked about using multiple conditions in VLOOKUP. Check out below articles for more ideas & tips:

 

About the author: Sohail Anwar is a Londoner who has spent over 10,000 hours applying Excel in his professional life and earns well over 6 figures as a result. Now he’s on a mission to teach professionals how to massively increase their earnings by learning and applying Excel like never before. Find out more about Sohail on Earn With Excel or  LinkedIn


Do you want to be awesome in Excel?

Free Excel tips book - joining bonus - Chandoo.org newsletter

Here is a smart way to become awesome in Excel. Just signup for my Excel newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

134 Responses to “The ultimate VLOOKUP trick – Multi-condition Lookup”

  1. Sophie says:

    Entertaining and informative! Thanks Sohail.

    • akshans says:

      Dear Sir,

      When i see your article .. i am shocked everytime ...... because your article is soo usefull.. everytime .

      thanking you and your team to give us helpful articles.

  2. MF says:

    Although helper columns seem not elegant, that's my preferred option indeed.

    Welcome to visit my post here for the topic
    http://wmfexcel.wordpress.com/2014/05/11/perform-vlookup-with-2-lookup-values-2/

    • Sohail Anwar says:

      Whoah! MF that is a pretty sweet article, love it!
      I do agree for the most part on helper columns but you know what beats helper columns? Just plain ol' helpers. Yep, work your way up and get some support staff!

      Jokes aside, where I would absolutely use the kind of approach in this article is for text stuff (personal preference), where I have lots of formulas involving COLUMN() and/or INDIRECT (rely on original column positions) and also if i'm using VBA which rely on original column numbers.

      • MF says:

        haha... i am the support staff in the office, i guess... 😛

      • Manjunath says:

        Hi I need a solution to this. Please help.

        This is in first sheet

        Column A Column B Column C
        MFRIL 2 MFN1024A
        MFRIL 3 MFN1024B
        MFRIL 2 MFN1024C
        CNFMK 1 MFN1024D
        FQLKP 4 MFN1024E

        This is in second sheet

        Column A Column B
        MFRIL 2
        MFRIL 2
        CNFMK 1
        FQLKP 4

        I need to get the values of column C of the 1st page in Column C of the 2nd page. I actually need MFN1024A & MFN1024C as first 2 lines has duplicate values.

  3. Michael (Micky) Avidan says:

    What about some shorter formula:
    =INDEX(C$2:C$5,MATCH(A8,A$2:A$5&" "&B$2:B$5))
    Micky Avidan

    • MURALI says:

      Yes Micky, when used as an Array Formula. Thanks.

      • Elias says:

        Murali,
        Post formula still an array. The only difference is that it isnot enter with Ctrl+shift+Enter.

        Also, a helper formula will work much faster than a concatenation inside of the formula.

        Regards

    • Sohail Anwar says:

      Mickey Avidan you are Da' Man, that is just awesome! Once I put in FALSE at the end as Roger suggests, it's good to go. I'm almost tempted to open up about 3 years worth of workbooks and replace about 1million of my formulas with your suggestion!

      • Krishna says:

        Thanks Sohail

        THanks for sharing this. But I prefer to use Index Match instead of VLOOKUP.
        Thanks to Micky fro sharing this formula

    • Anish says:

      GREAT!!!!!
      Simple and power packed Thank you very very much Micky...

  4. Jonathan James says:

    Thanks Sohail!
    I prefer to use INDEX MATCH instead of VLOOKUP because VLOOKUP is volatile, but I'm certainly going to have a go at combining multiple criteria in this way.

  5. Lee says:

    When there is more than one condition, I use sumproduct almost exclusively.

    For you specific problem I would use:

    =SUMPRODUCT(--(A2:A5="John"),--(B2:B5="Johnson"),C2:C5)

    Other benefits of sumproduct

    - you can look at not only exact matches, but also greater or less than conditions.

    - If you have multiple matches it will sum the value you are looking for.

  6. Andras Ujszaszy says:

    Hi Folks,
    This choose option is really stunning, however does not work with other regional settings.

    My Regional settings are Hungarian, so the decimal separator is "," and the argument separator in formulas is ";".

    I've tried to translate the formula, but got #REF! error. Stepping inside, I've realized that the arguments in curly brackets cause the error.

    if I type CHOOSE({1,2} - comma - the formula retrieves the first column (in this case the joined Aa series)

    if I type CHOOSE({1;2} - semicolon - the formula retrieves first item from the first row, then the second item from the second column, and #NA! for the rest

    Please help me to get rid on this issue.
    Your help would be very much appreciated

    Andras

    • Sohail Anwar says:

      Hi Andras,

      Thanks for the kind words. I'm afraid I have not experienced that problem. I would perhaps defer to the suggestions in here (i.e try \ instead of ; otherwise TRANSPOSE):
      http://www.pcreview.co.uk/forums/regional-settings-finding-out-array-separator-characters-t3942968.html

      Let us know how it works out!

      Thanks

      • Andras Ujszaszy says:

        Hi Sohail,

        Once again, I'd like to thank for this really great post and approach. Especially a VERY BIG THANKS for the help regarding the list separator issue under different regional settings. I've browsed truogh the provided links and I'd like to share the results, while some of you may find it useful.

        Accordind to my not-representative research there are two main types of regional settings whis affects the list separators.
        One is the let say English (containing UK, US, China, Australia, India and others) where the list separators are ";" and "," inside the curly brackets.

        According to the other is (let call European) settings the list separators are the follows:
        {1\2} lists row 1 from colum 1 then row 1 from colum 2 and so on
        {1;2} lists row 1 from colum 1 then row 2 from colum 2 and so on
        {1;2} lists row 1 from colum 2 then row 2 from colum 2 and so on

        Kindest regards,
        Andras

      • Mauro says:

        Mauro says:
        Your comment is awaiting moderation.
        October 9, 2015 at 5:07 pm

        Thanks for the “{1\2}”. I had hit a wall there.
        Reply

    • Timppa says:

      Andreas,

      Check the demofile. The syntax in there is =VLOOKUP($A7;CHOOSE({1\2};$A$2:$A$5&" "&$B$2:$B$5;$C$2:$C$5);2;0)

      {1\2} is the way to write it not 1,2}

      I am in Finland and the syntax for Excel dont use , but instead ;

      For this case correct syntax was {1\2}

  7. Pete says:

    Excellent tip! I really love these kind of over-the-top workarounds. Particularly interesting is the the use of CHOOSE, I've not come across it used in an array formula like that before.

    - That's the Excel geek in me replying.

    The pragmatic office employee in me will continue using helper columns - purely for spreadsheet readability. If one of my colleagues stumbled across an array formula like that they wouldn't understand it at all.

    As much as I love overly complicated formulae (especially array formulae!) I only do it when there is no other more simple alternative, or if the time saving is enormous.

    • Sohail Anwar says:

      Pete, a pragmatic high five coming your way! I too am a pragmatist. I've just accumulated far too much in my Excel arsenal. I won't lie, most of it you don't need as a professional. Your talk of time saving problem solving is music to my ears 🙂

      However, you're missing the point, sometimes its plain ego boostingly fun to look like a know it all Excel complicated formula wizard! j/k

  8. Pstloui says:

    As usual, an excellent tip and perfect timing! I am currently reading about arrays in Mike Girvin's excellent book "Ctrl+Shift+Enter", and this topic is right in step with it. However, the larger your dataset, the slower the Vlookup will execute. In fact, throw in several vlookup columns and your spreadsheet will grind to a near recalc halt. Also, if you have duplicate data, only the first instance will be retained in the result. So Mike also explores the use of index and match, a lookup join, use of DGET, a helper column, and even PivotTable, pointing out pros and cons and timings of all. It's a good book. See his thousands of free Excelisfun YouTube videos too.

    To make matters worse, there is the wonderful issue of GIGO at my work site. Example is one name shown as T De La Portilla, another as DeLaPortilla, T., another as Portilla, T. De La, etc. This just kills the vlookup-choose.

    Keep the newletters coming. I look forward to each one.

    • Sohail Anwar says:

      Pstloui, great food for thought, I appreciate the insightful comments. For sure 'Performance' is always a consideration. My rule of thumb is for every 1Mb of file size, your approach potentially needs changing. Especically if you've got 1000's of formulas and double especially if they are processor power consuming Array Lookups. Then bring on the helper columns + pastespecial Values.

      Also, as much as i complain, dealing with GIGO (Garbage in garbage out for the uninitiated) has kept my skills in demand so I can't entirely complain ;-P

  9. Herbert says:

    Hi Shohail,
    Great trick!!! Thank you for sharing.

    I have always used the VLOOKUP formula in combination with helper columns in multi dimensional lookups. With 2-dimensional lookups I have lately also started using the INDEX formula in combination with the MATCH formula. With a more than 2 dimension lookup the trick presented will indeed be very helpful.

    Can you help me understand why
    the array formula {=VLOOKUP(A13&A14&A15, CHOOSE({1,2}, A2:A10&B2:B10&C2:C10, D2:D10), 2, FALSE)}
    gives the same result as the array formula {=VLOOKUP(A13&A14&A15, CHOOSE({1,2,3}, A2:A10&B2:B10&C2:C10, D2:D10), 2, FALSE)}

    Note that the difference in the first parameter of the CHOOSE function used in both formulas.

    The first formula is used in the demonstration of your first example, while the second formula is in accordance with the simple structure layed out before the demonstration in the second example. The formula used in the example does not follow this structure, but still gives the desired result.

    Regards,

    Herbert

    • Sohail Anwar says:

      Cheers Herbert! Well done on your progress my friend 🙂
      As for your question. The key component of the array formula is the {1,2} in the Choose. It instructs the CHOOSE to generate 2 ranges for the vlookup to use (1 containing the concatenated range A2:A10&B2:B10&C2:C10 and the other containing D2:D10. Now if you put {1,2,3}, the CHOOSE attempts to create 3 ranges, but you haven't declared a 3rd option in the CHOOSE so Excel just assumes a #N/A error. so we have 3 ranges that the VLOOKUP checks out, since we have told the VLOOKUP to look in the 2nd range, it doesn't care what's in the 3rd. So even if you put {1,2,3,4,5} it will still only care about 1,2 (and give result from 2).

      As a learning tool to understand Arrays play with the structure: i.e
      {=VLOOKUP(A13&A14&A15, CHOOSE({1,2,3,4}, A2:A10&B2:B10&C2:C10, 1,2,D2:D10), 4, FALSE)}
      will give you the correct result (but it takes the scenic route). I would strongly encourage the use of 'Evaluate Formula' and stepping through. Use small arrays (3 rows of data max) and it will make it a bit easier to grasp.

      Hope that helps!

  10. Roger Govier says:

    Hi

    Nice solution, Sohail.
    It also works with Tables, either using structured references
    =VLOOKUP(A7,CHOOSE({1,2},Table1[First Name]&" "&Table1[Surname],Table1[Uniqe Reference]),2,0)

    or by using the absolute formulae as you used.
    The advantage of ocurse, if it is a table, and even though the references look to be fixed, the range will grow as more data is added to the table.

    @Micky
    I think you need to add the final parameter of 0 or FALSE to your formula to get consistent results.
    Your solution gives the correct result by pure chance.
    If you have any multiples of names in the list, then your formula as it stand will not find the first occurrence.

    @Jonathan
    I also prefer Index, Match, but Vlookup is NOT one of the volatile functions in Excel.

    Regards
    Roger

  11. Jeff Weir says:

    Awesome post. I'm going to echo Sophies comment above and give this post the best feedback there is:
    Informative AND funny.

    Look forward to more of the same.

  12. Louise says:

    Is this going to be any faster than index/match though? I have been index/matching the concatenation of three columns against a different concatenation on another sheet. It seems that vlookup combined with array formulae will be slower.

    • Sohail Anwar says:

      Hi Louise, good question and I have to declare two things I strongly believe in:
      1. If it ain't broke, don't fix it
      2. Quick turnaround for the win!

      So if you have a process and approach that works for you, carry on!

      If you're dealing with 1000's of formulas, than yes this will be slower. Have a look at my reply to MF as to when I use them.

      Thanks for the questions!

  13. Terry says:

    Great post. Thank you

  14. Sohail Anwar says:

    Thank you folks, you have all utterly delighted me with your incredibly kind words. With your support and encouragement I hope to continue edu-taining!

  15. Mark Duchesne says:

    Hi Sohail,

    Really clever trick, never knew you could do that with vlookup and choose. I use arrays a lot and have used choose before but are struggling to understand the choose part of the solution.

    So the CHOOSE({1,2},$B$2:$B$5&", "&$A$2:$A$5,$D$2:$D$5) will create two ranges, how does choose process these two ranges to give us one array/table for looking up?

    I read your response to Herbert but are still struggling.

    Thanks

    Mark

    • Sohail Anwar says:

      Hey Mark,

      Thanks for the comments. I appreciate the probing question. Let’s break it down like this, The vlookup only like contiguous i.e going left to right and up to down whilst being joined up (i.e. A1:J10, not A1:C10&F1:J10) but we are essentially dealing with non contiguous ranges, i.e B then A, with a “,” to add to the fun. We need to turn this mess into a contiguous range (or the Vlookup will have a hissy fit!). This is where the wonderful choose comes in. Not only does Choose produce static values or formulas but it can also spit out ranges. The Choose plus the array takes all that and concatenates all of it and spits out a virtual 1 column containing B&”,”&A. the {1,2} tells the choose that the range it outputs (i.e Range1:Range2) will be made from the 1st and 2nd values (or choices) in the Choose, so we get Range1 as B2&”,”A5 and Range2 as D2:D5. So we have now got the following Vlookup(“Johnson, John”, Range1:Range2,2,0), which is simple to solve! So when Herbert asked about {1,2,3} this was creating 3 ranges: Range1 as B2&”,”A5 and Range2 as D2:D5 and Range3 as #N/As (because we only have specified 2 options in the choose, if you put a 3rd option like1 after the D2:D5, then Range3 will become 1s. Of course the Vlookup doesn’t care because it is only ever going to look in Range2 for the answer.

      I hope that helps, its tricky to explain via a plain text editor, especially for a visual person like me! If you’re London based I’m happy to meet for a coffee and go through it some more!

      • Mark Duchesne says:

        Thank you Sohail for your reply, your explanation does help. I will continue to play with the function to see how i might be able to use it.

        I love learning new ways to do things.

        Thanks for sharing this with me.

        I am based in Australia so we will have to have virtual coffee 🙂

    • NARAYAN says:

      Hi Mark ,

      I think the concatenation is coming in the way of understanding the basic part played by the CHOOSE function.

      If we consider just the basic CHOOSE formula , we can have something like this :

      =CHOOSE({1,1},3,4,5,6,7,8,9,10)

      I have deliberately inserted more values than are required for the CHOOSE function , to show that Excel will not complain ; Excel will complain if the values are less in number. Thus , the following will generate an error :

      =CHOOSE({1,2},3)

      since there is no second value to choose. However , if we shorten the first formula to :

      =CHOOSE({1,1},3)

      Excel will not complain , since all we are doing is choose the same value twice.

      To see what the above formula does , wrap it inside SUMPRODUCT :

      =SUMPRODUCT(CHOOSE({1,1},3))

      You should get 6 ; how is it 6 ?

      Because the CHOOSE function is creating a virtual array { 3,3 }.

      If we move on to a more complex formula , as in :

      =CHOOSE({1,2},1+(ROW(1:8)-1)*2,ROW(1:8)*2)

      what Excel does is create a virtual matrix of 8 rows and 2 columns , taking one item at a time , alternating between the first range and the second range.

      The first range is an array of odd numbers 1 , 3 , 5 , 7 , 9 , 11 , 13 , 15 ; the second range is an array of even numbers 2 , 4 , 6 , 8 , 10 , 12 , 14 , 16.

      The virtual array we will end up with is :

      {1,2;3,4;5,6;7,8;9,10;11,12;13,14;15,16}

      If you change the above formula to :

      =CHOOSE({2,1},1+(ROW(1:8)-1)*2,ROW(1:8)*2)

      you can easily understand that you will have the virtual array :

      {2,1;4,3;6,5;8,7;10,9;12,11;14,13;16,15}

      where the first column of the virtual matrix consists of numbers from the second array , while the second column of the virtual matrix consists of numbers from the first array.

      Narayan

      • Mark Duchesne says:

        Thanks Narayan for the other examples of how the function works.

        It just goes to show you how many next things you can do in excel. I havent used the choose function to create a virtual array so its a new thing for me.

        Will take a look at your examples now!

        Thanks

        Mark

  16. Chandra Mohan Singh says:

    Wow!!! Very Very useful......Thank you very much Sohail and Chandoo.....

  17. Manoj Varghese says:

    Wonderful... This really helped Thanks

  18. Khalid NGO says:

    Wow... Awesome!
    Excellent stuff Sohail.
    Interesting to work with vlookup+choose...

    Many Thanks for the wonderful trick.

  19. Dennis Del Villar says:

    Great tip and hats off to all...maybe this trick can be used in table/example No.3:

    =LOOKUP(2^4,SEARCH(A3&B3,A2:A7&B2:B7),C2:C7)

  20. chris says:

    Ever need to find a value in a range but dont want to bother identifying the row and column every time? Try a double axis index match.

    =index($b$2:$z$10,match(rowvalue,$a$2:$a$10,0),match(columnvalue,$a$1:$z$1,0))

    Typed on my phone so forgive minor errors... love me some index match.

    Chris

  21. Dana Bell says:

    You do have to be careful with concatenation. When you concatenate AB & C, you get ABC. When you concatenate A & BC, you also get ABC. Make sure the result is unique or you may get wrong answers.

  22. Wookiee says:

    Thank you, Sohail! That was a great article. Not only was it clear, concise, and easy to follow, but the humour and references to 80s movies was a nice touch.

  23. Rubén says:

    Estimado Chandoo, buenos días.

    Excelente!

    Te sigo desde hace años, y este truco, sinceramente, no lo sabía, ni siquiera lo imaginaba.

    Muchas, muchas, muchas gracias.

    **********

    Dear Chandoo, god morning.

    Excelent!

    I follow you years ago, and this trick, I don't know about that, sincerely.

    Lots, lots, lots thanks.

  24. Alex Groberman says:

    If you use index/match and wrap the match in a sumproduct, there's no need for ctrl+shift+enter:

    =INDEX(C2:C7,SUMPRODUCT(MATCH(E2,A2:A7&B2:B7,0)))

    Regards,

    Alex

    • Alex Groberman says:

      Actually this is better:

      =INDEX(C2:C7,MATCH(E2,INDEX(A2:A7&B2:B7,),0))

      Wrapping "A2:A7&B2:B7" in an index function with a null second parameter coerces the reference into an array. Sumproduct does the same, but just seems less 'clean' to me.

      -Alex

    • Debbie says:

      Hi Chandoo,

      We use a purchasing factor table that inflates or deflates the price depending on the quantities being ordered. I have used h and v lookup in stages to date, but now I am going to try and do it all together, got me thinking! Some comment above that may help.

      Debs

  25. Ranveer Singh says:

    Nice Article for v lookup. i have tried to apply these new technique but faced some issue as did not get the correct result

  26. Zaigham says:

    Excellent..... Thanks Sohail.

  27. db says:

    great tip, thanks! I was wondering how the formula would change for hlookup(). i wouldn't think it would be substantially different, but i can't seem to get it to work. vlookup() version is just fine.... any ideas?

    • NARAYAN says:

      Hi ,

      The same technique would work if you wanted to use HLOOKUP.

      However , for HLOOKUP to work correctly , your data layout would have to be changed to suit.

      Thus suppose all the data was laid out in rows , with :

      a) The first names in A12 through D12 ,
      b) The surnames in A13 through D13 ,
      c) The unique references in A14 through D14

      the equivalent formula to retrieve the unique reference corresponding to John Johnson would be :

      =HLOOKUP($A7,CHOOSE({1;2},$A$12:$D$12&" "&$A$13:$D$13,$A$14:$D$14),2,0)

      Similarly , the equivalent formula corresponding to Johnson, John would be :

      =HLOOKUP($A8,CHOOSE({1;2},$A$13:$D$13&", "&$A$12:$D$12,$A$14:$D$14),2,0)

      Both of the formulae would be array formulae , entered using CTRL SHIFT ENTER.

      Narayan

  28. xen says:

    While this method might seem to be more elegant, helper column is much more effective. Because your way creates 'virtual helper column' for every cell where formula is. And if you have lots of data, that can become quite a problem.

  29. Jorge says:

    Just when you thought you were getting good at Excel, comes this post... and the comments! Excel Rocket Science! Good!

  30. Excel-er says:

    You can do with Chandoo!

  31. VENKAT says:

    this also will work particularly loo up reference is not in the leftern most column of relevant range.

    =INDEX(C1:C5,MATCH(1,(A1:A5=A2)*(B1:B5=B2),0))

  32. Bag'oong says:

    Good Golly, Miss Molly! Has this ever sorted me out!!!

    Often with a wide data table, I need to create multiple helper columns at the left, or at least to the left of the useful bits.

    And then because you have to share the results with a user (or heaven forbid someone who thinks he's the Excel expert), you hide the helper columns away so the poor thing doesn't get confused. Then ... user/expert discovers them (he actually notices that there's a B missing beteen A and C), unhides them, doesn't like them and deletes them ... and everything goes pear shaped until you painstakingly repair it!!!

    One of the useful things about this approach is that user/expert will see how complex the formula looks, get frightened of changing it, and leave it alone!!!

    Hopefully.

  33. Rajesh Panwar says:

    and what if you have multiple lookup value with same criteria. like Bb in multi time entry.
    A Column Another Column Yet Another Column
    A a 12
    B b 24
    C c 36
    D d 48
    B b 60
    F f 72

    Bb 84 =SUMIFS(C:C,A:A,LEFT(E2,1),B:B,RIGHT(E2,1))
    Dd 48 =SUMIFS(C:C,A:A,LEFT(E3,1),B:B,RIGHT(E3,1))

    • Sohail Anwar says:

      Hi Rajesh,

      I have submitted another article to Chandoo which addresses your question on multiple occurrences and also multiple values. But as a spoiler, here's a formula that will help you find all values for Bb, put this in F2 with Ctrl + Shift + Enter and drag down
      INDEX($C$1:$C$6,SMALL(IF($A$1:$A$6&$B$1:$B$6=E2,ROW($A$1:$A$6)),ROW()-1),1)
      I have left out IFERROR so you will get #NUM! errors after the 2nd entry.

      All will be explained in the article!

      • Timppa says:

        Instead of using row in the end of formula i have used
        -> COUNTIF($U$4:U4;U4) (Results into T column)

        As additional help column.

        Here is the syntax as whole:

        IF(ISERROR(INDEX('KHH YHTEENVETO'!$D$11:$P$44;SMALL(IF('KHH YHTEENVETO'!$D$11:$D$44=Poikkeutus_summary!U4;ROW('KHH YHTEENVETO'!$D$11:$D$44));(T4))-10;7));"";INDEX('KHH YHTEENVETO'!$D$11:$P$44;SMALL(IF('KHH YHTEENVETO'!$D$11:$D$44=Poikkeutus_summary!U4;ROW('KHH YHTEENVETO'!$D$11:$D$44));(T4))-10;7))

        in this syntax T4 contain the cumulative number for gathering data.

  34. Sundar says:

    You article is very gooooooooooooooooooooooooooooood!!!!

    After reading it i have started kicking out the helper column from all my excel files ....

    Thnx a lot...

    If you can spare sometime i can send you my excel file in which i am maintaining my companys inventory and wanted you to optimize that file.

  35. Nishad says:

    It is very helpful and will solve lot of problems i was facing

  36. Pankesh says:

    Hi,

    I want to know about multiple conditional
    COUNT COUNTIF Function which count girls and boys or male female or first three no. Fail and passed candidates somebody help me or give example to use it in school result report

  37. […] ein paar Wochen habe ich bei Chandoo über einen Trick gelesen den ich noch nicht kannte aber ziemlich interessant […]

  38. Mahannad Madani says:

    Dear Sir

    I would like to ask about the third methodology of extract the name regardless the form of the name. Ok what about if it was the opposite?, for instance, if the spreadsheet was the name in column and the surname in another column and my target is to combine them in one cell.

  39. Excel Challenge says:

    Does this VLOOKUP and CHOOSE function also work when referencing values in other tabs/sheets? I am having trouble getting my formula to work- any help would be appreciated! THANK YOU!!!

  40. Amol Kaushik says:

    We are able to correlate all the arguments apart from column index number which is 2,if you just give some suggestion which can help me to understand the functionality.

  41. kugli says:

    Hi there,

    That's an awesome article. Very helpul, thanks. I have one question though. I want to use the 1st formula in your post and vlookup the final value by using a MATCH function. However, I have realized that your formula seems to be only able to deal with looking up the 1st or 2nd row specified? Can you please show me how to amend your formula in order to look up any column value by using a MATCH function for the vlookup?

    Thanks so much!

  42. Jackie says:

    You are amazing, I bow down to your mad excel skills. Long live the king

  43. Ahmad says:

    Thank you Sohail 🙂

  44. Ahmad says:

    Thanks Chandoo!

  45. Thiago says:

    I love you man. THe answear I was looking for! Thank you so much!

  46. A says:

    Entertaining and mind-blowingly simple to use.

    Thanks for sharing this tip, Sohail!

  47. Oakley says:

    Thanks Sohail!! Very entertaining and informative! The right mix!!

  48. Martin says:

    Thank you very much...that was exactly what I have searched!

    Can I also use the choose function above in cooperation with an indirect.ext? Because the combination of vlookup + choose need to grab the data (result status) out of some reports (closed workbooks on a network share).
    Let me explain my request.
    Main.xls
    Column A = Names
    Column B = Numbers

    Reports.xls stored on a share
    Column B = Names
    Column C = Numbers
    Column E = Result --> that is exactly the info which I have to grab.

    I need to search for the mapping of NamesNumbers in closed workbooks, as I do require the result status.

    I would highly appreciate in case anybody has an advice.

  49. Ravi says:

    Hi, have been trying out your vlookup with concatenate for multiple column selection but somehow not getting there as result is zero.
    am trying to do as below
    column b1,c1 is to be concatenated and vlooup in another sheet where it need to be lookup to similar b1,c1 column and then return say column no 5 of the data... also iferror is added by me becz the value being looked up not necessary be in table that is being lookedup to..
    please could you help ?

  50. Puneet says:

    Hi

    I am trying to use this formula of multiple conditional v lookup & every time getting error as #N/A. I am reproducing my formula. if anyone can help.
    =VLOOKUP(A2&B2,CHOOSE({1,2},Sheet2!A:A&Sheet2!B:B,Sheet2!C:C),2,0)

    Pl help.
    Regards
    Puneet

    • TKKREDDY says:

      Press Crtl + Shift + Enter Dude !!

    • Steve says:

      Is it true that Array formulas wont transcend across separate worksheets ?

      • Hui... says:

        @Steve
        No, its not true
        Some Functions can handle multiple worksheets and some can't
        Even those that can't can sometime be coaxed into working across multiple worksheets by using sneaky methods

        I will actually be publishing Formula Forensics No 38 this week and in that we look at finding the Maximum Value across multiple worksheets and the actually finding which worksheet it is on. This involves using the Index function, which doesn't natively handle multiple worksheets, yet can be coaxed into returning the correct answer with a workaround

        If you have a specific requirement let us know in the comments

        • Steve says:

          HUI,

          Dang I love you man !!
          With your comment above I endeavored to persevere to make my Multi Condition vLookup transcend to another worksheet.

          It was tricky .. I also found that assembling the Array in the formula works.. in my case the Vlookup source was not in the correct column order, so assembling them A&B&C etc wouldn't work.. I had to assemble my Array B&G&D to get the correct result.

          Here is my vLookup, notice COST! is another worksheet, and Array is B&G&D:
          {=VLOOKUP(B4&$K$1,CHOOSE({1,2},COST!B:B&COST!G:G,COST!D:D),2,0)}

          Thanks very much !!
          Steve

          • Steve says:

            PS..

            Experiencing a 15 to 20 second workbook Re-Calculating with any change to moved forward in attempting to speed up the Array ..

            Notice my previous formula was looking at the entire columns, after I modified that to look at only the areas of data there is no noticeable lag time .. it's literally milliseconds now.

            Modified formula, notice absolute cell references using $:
            {=VLOOKUP($B4&$K$1,CHOOSE({1,2},COST!$A$1:$A$50&COST!$F$1:$F$50,COST!$C$1:$C$50),2,0)}

            Thanks everyone.. this is a great resource!
            Steven

          • Hui... says:

            @Steve
            You are now recalculating 1/20,000th of the the data you were before
            It is always poor structure and use of inappropriate formula that slow spreadsheets down

            Good job in solving your issue

  51. Dana Reed says:

    EXCEL 2013 - I am trying to Compare Column A to Columns B,C,D for matches and put location of match in column E. Is this possible?

    I love the article!

  52. TKKREDDY says:

    Chandoo.

    Thanks, even though I took your online class some time ago. I couldn't concentrate much.

    Now along with the above, the one condition of the Vlookup I rquire is date, there are multiple dates, but I want the latest dated value.

    which one to use ?

  53. SoftBatch says:

    This is the best VLOOKUP solution! It works so well! Thanks so much for the easy to understand solution!

  54. Pawel says:

    Excellent job! Nice trick:)
    I have compared operational times for INDEX MATCH and then for this VLOOKUP CHOOSE.
    First one requires much less time to calculate (I'm using whole columns in parameters like E:E instead of range E1:E2000) and then filling hundreds of lines with formula.
    But good to exercise this one too 🙂

  55. Mahesh says:

    Hi, I need a vlookup formula in below scenario where i need to pull data from another sheet(2) where only SKU code is available and in sheet(1) where SKU is repeated but of different MFD. I need to pick the oldest stock only is it possible to put vlookup..
    VLOOKUP SHOULD CONSIDER 2 COLUMNNS SKU & MFD..And should Qty should appaer only against old MFD
    Sheet 1
    SKU Disc MFD Qty
    02N JW 100 CL 21-Jul-16
    02N JW 100 CL 22-Jul-16
    02N JW 100 CL 26-Jul-16
    02N JW 100 CL 26-Jul-16
    02N JW 100 CL 31-Jul-16

    Sheet 2
    SKU Disc Qty
    02N JW 100 CL 1

  56. Hahnzus says:

    I normally never comment on these type of articles, however I cannot ignore the greatness that is an Ivan Drago reference. Well done sir, well done

  57. Amit says:

    Table No Start date End date Society Name
    1 1/7/2015 3/7/2015 ABCD
    2 2/7/2015 5/7/2015 PQRS
    1 5/7/2015 7/7/2015 ABCD
    1 7/7/2015 10/7/2015 XYZ
    HI ,

    My query is that if i have to develop a vlookup with multiple criteria such that if Table No is 2 and the satart date and End date lies between the mentioned dates then the society name should be pulled out.
    Can you please help!!

  58. Ali says:

    Thanks Sohail, this formula is working great although a bit slow as I have more than 800 rows containing this formula. Thumbs up!

  59. RadekB says:

    Nice function, clever and clear. But... for my 100 thousand row table and three criteria it kills my computer premanently 🙂

  60. Tim says:

    I will stick with my helper columns as it is more intuitive. I have copied this formula exactly and am getting #N/A. the 15 minutes I have wasted on this could have been spent on a hundred Helper Columns.

    • RadekB says:

      It's a matter of practice and patience :). If You are getting #N/A and You know that data is O.K, there is no other way than to find out what is wrong. In my case I had to change original one:

      =VLOOKUP(A13&A14&A15,CHOOSE({1,2},A2:A10&B2:B10&C2:C10,D2:D10),2,0)

      into

      =VLOOKUP(A13&A14&A15;CHOOSE({1\2};A2:A10&B2:B10&C2:C10;D2:D10);2;0)

      Then it worked.

  61. […] The above is the simplest approach to a multiple criteria VLOOKUP. There are more elegant approaches that need not a Helper Column e.g. check-out Chandoo’s example here. […]

  62. masoodarvish says:

    thank you chandoo
    thank you sohail
    this article is exactly nice

  63. Mauro says:

    I believe

    VLOOKUP(lookup value, CHOOSE({1,2,...N},Column1 & Column 2 &…& Column N, Result Column),2,0)

    should read

    VLOOKUP(lookup value, CHOOSE({1,2},Column1 & Column 2 &…& Column N, Result Column),2,0)

  64. […] The above is the simplest approach to a multiple criteria VLOOKUP. There are more elegant approaches that need not a Helper Column e.g. check-out Chandoo’s example here. […]

  65. Manfred_uk says:

    Lovely stuff

  66. Daffodil says:

    Hi,

    I am stuck with a problem and would like some help. I thought this may be a right place to ask my question.

    tab 1
    col name1
    red
    green
    blue
    black

    tab 2
    col1 col2 col3 col4
    red blue red red
    blue green blue green
    black

    This is the result I want
    tab 1
    col name1 col name2
    red col1
    green col2
    blue col1
    black col3

    what I'm looking for is to search for exact string in a different tab and return values of the corresponding column name where it finds it first. Does anyone have a solution for this?

    Thanks!

  67. Amartiny says:

    Hi,

    I follow this post and I can tell this multi condition vlookup works nice when using the A1 references, but I am unable to make it work with R1C1 notation.

    In my application i typed
    =VLOOKUP($D$4&D7,CHOOSE({1,2},'services 60 days'!$F$4:$F$200&'services 60 days'!$I$4:$I$200,'services 60 days'!$L$4:$L$200),2,0)
    As an example it would be VLOOKUP(A&7,CHOOSE({1,2},A:Z&1:200,'1-Jan:31-Dec),2,0)

    This formula reads from another sheet and works perfectly.
    But when I type it and Ctrl+SHft+Enter I get an error. The RC references are ok and point to the same cells.
    =VLOOKUP(INDIRECT("C[1]",FALSE)&INDIRECT("C[2]",FALSE),CHOOSE({1,2},'services 60 days'!$F$4:$F$200&'services 60 days'!$I$4:$I$200,'services 60 days'!$L$4:$L$200),2,0)

    What I have seen is that (INDIRECT("C[1]",FALSE)&INDIRECT("C[2]",FALSE) provide the same info as $D$4&D7, A7

    CHOOSE({1,2},'services 60 days'!$F$4:$F$200&'services 60 days'!$I$4:$I$200,'services 60 days'!$L$4:$L$200)
    when calculated independently in a cell provides the good results for the choose() with Ctrl+SHft+Enter, --> A:Z&1:200

    but when included into the multi vlookup function it returns a different range, so the overall vlookup is N/A because it looks for A, in a range AA:AZ

    Could it be the issue is because of choose being an array not being updated properly? Anything not right in the syntaxis?

    Thanks,
    Alberto

    • prem says:

      i want to do a vlookup for date wise for exp given below

      mobile number 1st 2nd 3rd
      xxxxxxxx 5 5 6

      kindly help me to get the formula

  68. Ted says:

    Sohail, thanks for the great tip! However I have run into a problem, where I am getting a #VALUE error and I have not been able to figure it out.
    I am trying to write a program for my daughter, who is a loan office at a local bank to select the correct Loan Rate based upon the Model Year, Loan Term and Points.

    I have the following input data cells:
    B7 = Model Year of Auto (num) - USER INPUT
    B8 = Loan Term in Months (num) - USER INPUT
    B9 = Points to determine Loan Rate (num) - USER INPUT
    B10 = Loan Interest Rate (%) - Value I am trying to return from data table

    I also have a data table where I have the following data
    S6:S86 - Mdl Year of Auto (num), 16 rws for ea year, sorted ascending
    T6:T86 - Loan Term (num), 4 rws for ea TERM x 4 different TERMS (72,60,48,36) x 4X, sorted ascending
    U6:U86 - POINTS (num), 4 rows for each Term x 4 different Terms, sorted ascending (4,7,10,13) x 4X
    V6:V86 - RATE (percent), This is the value I am trying to return to B10

    B10 Formula - {=VLOOKUP(B7&B8&B9,CHOOSE({1,2},S7:S86&T7:T86&U7:U86),2,0)}
    In the step-by-step Evaluate Formula, shows this VLOOKUP("20137210",{"2004364",#Value!;"2004367",#Value!;"20043610",#Value!;"20043613",#Value!;................this repeats 4X for each term with the year & term values changing.

  69. sachin ubale says:

    Namaste ,

    I have been using this formula, it works for me, but now i have increase the data in my answer cell, and the formula forced to give me ans for false condition.

    Please let me know how to resolved this issue.

  70. Vincent says:

    Hi,

    Can someone assist me with my query?

    I have a list of 199 names in one column and a list of 2573 names in another column that I need to match and them sum the multiple commissions to the 199 names given.

    I need to match the names and add all the commission amounts to the 199 names.

    Please assist

    Vincent

  71. Vincent says:

    Good Morning Chandoo,

    Can someone assist me with my query?

    I have a list of 199 names in one column and a list of 2573 names in another column that I need to match and then once the names have been matched, I need to sum any matches by name with another column with commissions. Some of the commissions are duplicated hence the need to match the name columns (NB: some of the names don't match exact).

    Much appreciated if someone could assist me with my query to match the 2 name columns and add all the commission amounts to the 199 names.

    Regards
    Vincent

  72. darshana kesareea says:

    Hi Sohail & Chandoo

    Your above formula is the one i am searching
    I have to match 2 columns & then take the value
    data in sheet 1
    Col A Col B Col C
    Conveyors 1 Row 15000
    Conveyors 2 Row 17000

    Sheet 2
    when i type VLOOKUP($E2,CHOOSE({1,2},$A$2:$A$7&$B$2:$B$7,$C$2:$C$7),2,0) and press Ctrl + Shift + Enter no help

    pl. help
    darshana kesareea
    Conveyors 1 Row = i want the value 15000 to appear

    i tired your above formula , but i get an error"NA"

  73. Jeremy R says:

    Really like this - great idea of being able to combine search criteria. I was beating my head against the wall b/c I can't seem to make this work on Google Spreadsheets - any ideas why? It worked great on excel but can't seem to make it carry over to google spreadsheets where all of my data is.

    I tried just simplifying the formula to see where it was breaking and google spreadsheets gives me "#REF" error on this formula below. It evaulates to "b" on excel. Any ideas?
    =VLOOKUP("a",CHOOSE({1,2},"a","b"),2,0)

    Data below - using "FICO, LTV and TERM - i'm trying to get the MI Factor.

    Fico LTV Term MI FACTOR
    Excellent 0.8 360 0
    Excellent 0.85 360 0.0023
    Excellent 0.9 360 0.0039
    Excellent 0.95 360 0.0054
    Excellent 0.8 180 0
    Excellent 0.85 180 0.0021
    Excellent 0.9 180 0.0029
    Excellent 0.95 180 0.005
    Good 0.8 360 0
    Good 0.85 360 0.0027
    Good 0.9 360 0.0044
    Good 0.95 360 0.0062
    Good 0.8 180 0
    Good 0.85 180 0.0025
    Good 0.9 180 0.0034
    Good 0.95 180 0.0057
    Fair 0.8 360 0
    Fair 0.85 360 0.0033
    Fair 0.9 360 0.0057
    Fair 0.95 360 0.0089
    Fair 0.8 180 0
    Fair 0.85 180 0.0029

  74. Rahul says:

    I have an issue with this formula.

    Excel 1: Contains Roll Nos, Subject that he has taken (3 subjects attached with each roll no, namely: English, Hindi & Maths) and His Marks in each of those subjects in YEAR 2015. Ex:
    Roll Sub Marks15
    101 Hindi 72
    101 English 60
    101 Maths 78
    102 Hindi 71
    102 English 69
    102 Maths 32

    Excel 2: Contains Roll Nos, Subject that he has taken (Again SAME 3 subjects attached with each roll no, namely: English, Hindi & Maths) and His Marks in each of those subjects in **YEAR 2016**. Ex:
    Roll Sub Marks16
    101 Hindi 75
    101 English 62
    101 Maths 70
    102 Hindi 75
    102 English 68
    102 Maths 32

    My problem is to use one formula in sheet for YEAR 2016 and fetch the corresponding marks of each of the ROLL nos for all 3 of his subjects in Year 2015 and put them right in beside his marks achieved in the Year 2016. Ex, the result should look like

    Roll Sub Marks16 Marks15
    101 Hindi 75 72
    101 English 62 60
    101 Maths 70 78
    102 Hindi 75 71
    102 English 68 69
    102 Maths 32 32

    I am unable to get the results of any of the formulas above and even no results with using Index and match too. Pls help.

  75. vishal Sharma says:

    best lookup for me thanks really

  76. Maruti Thakur says:

    Parameters:
    Item Start Date End Date Value
    A 01-Apr-11 02-Feb-12 2
    A 03-Feb-12 01-Mar-12 3
    A 02-Mar-12 31-Dec-13 4
    A 01-Jan-14 31-Jan-14 5
    B 09-Jan-13 04-Apr-14 6
    B 05-Apr-14 07-Feb-15 7
    B 08-Feb-15 01-May-16 8
    B 02-May-16 01-Jun-16 9

    Item Date Value
    A 30-Apr-12
    A 05-Feb-12
    A 30-Jan-14
    B 07-Apr-14
    B 20-Feb-15

    Please help for for the above value column based on provided parameters.

    My mail id is maruti.thakur@gmail.com

  77. Graham says:

    could someone translate this into VBA as a application.worksheetfunction???? this is exactly what im looking for but cant seem to translate it to vba and I don't want to use formular1c1.

  78. Prem Prakash says:

    Lets consider column
    A B
    steel spoon, plate, rod, bus stand

    wood door, window, table, chair

    I want that if my Column
    C has D should return
    plate steel
    spoon steel
    chair wood

    How to do this?

  79. Prem Prakash says:

    Lets consider column
    A | B
    steel | spoon, plate, rod, bus stand

    wood | door, window, table, chair

    I want that if my Column
    C has | D should return
    plate | steel
    spoon | steel
    chair | wood

    How to do this?

  80. Hasan says:

    Regarding the delimiter within {1,2}

    As far I could find out, various language related settings make it very tricky to get the correct delimiters: Regional settings in Windows and Excel option regarding the use of decimal and thousands separators, and maybe also Excel language itself.

    So I wrote a simple formula to create the following 2r x 3c matrix (as different delimiters are used for rows and columns), here are the results:

    A B C
    1 2 3

    RegionalSettings: German (Austria)
    Excel Settings: Language German; UseSystemOperators
    ={"A"."B"."C";"1"."2"."3"}

    Excel Settings: Language German; UseSystemOperators=False, DecSep = . and ThsSep = '
    ={"A"\"B"\"C";"1"\"2"\"3"}

    RegionalSettings: English (Great Britain)
    Excel Settings: Language German; UseSystemOperators
    ={"A"\"B"\"C";"1"\"2"\"3"}

    Clearly, you will never be allowed to use the decimal separator (which is the "," with standard German settings) as any of both array delimiters as you can insert decimal numbers instead of text values into the array.

    Interestingly, even with Windows regional settings on English, I do not get "," as column delimiter (as in the example of Sohail)

  81. Irvin says:

    Link exchange is nothing else but it is just placing the
    other person's website link on your page at appropriate place and other person will also do similar
    in favor of you.

Leave a Reply