fbpx
Search
Close this search box.

The ultimate VLOOKUP trick – Multi-condition Lookup

Share

Facebook
Twitter
LinkedIn

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

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

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

    • Mohit Gupta says:

      How to Concatenate and lookup means how can i join two lookup result
      a b c
      13 Mohit Gupta

      I WANT TO LOOKUP through 13 and get the look result in one cell or concatenate b and c

  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.

  82. Sam Hamilton says:

    How can I use the 3 column VLookup formula with a sum function?

    i.e. using the example in the "Bye Bye Helper Column, it was nice while it lasted." section, how could I sum and return multiple of the values in the 'Savings Produced' Column (D)?

    Thanks so much!!

  83. Arun says:

    Hi Chandu,
    I tried to use above formula for cell reference in second file, Its providing Error. Can you please let me know how can I tackle this

  84. JM says:

    I managed to get this to work, but need to copy the formula down the column so the other items can be looked up. I keep getting an error message that says "Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated". Also, the formula results aren't correct for most of the cells I copied it to. Any idea why?

  85. Test says:

    Such a Good tip! alert(123)

  86. Balwinder singh says:

    =VLOOKUP($E$2&A7,CHOOSE({1,2},'dec16'!$A$2:$A$2000&'dec16'!$B$2:$B$2000,'dec16'!$H$2:$H$2000),2,0) WHEN USE I HAVE #NA IN SOME COLUMAN WHAT I CAN DO FOR THIS #NA

  87. Pavan H P says:

    Hi

    I need help for Vlookup formula

    in one sales order two deference part code are there and the part code are reputed in other sales number so i need price for part code which was billed in sales no.

    Please need help for this i have the more than 10000 list are there and i'm strangling to lookup

    sales number Purchase NO Part code Rate
    5210001010 5290001771 F01U216216 =vlookup(
    5210001010 5290001771 F01U251672
    5210001245 5290001561 F01U908172
    5210001245 5290001561 F01U110889
    5210007256 5290001861 F01U624555
    5210002452 5290001782 F01U110220
    5210002452 5290001782 F01U110220
    5210007262 5290008282 F01U216216
    5210001324 5290008811 F01U219876

    sales number Purchase No Part code part code
    Price
    5210001010 5290001771 F01U216216 4100
    5210001010 5290001772 F01U251672 1000
    5210001245 5290001561 F01U908172 2000
    5210001245 5290001561 F01U110889 3200
    5210007256 5290001861 F01U216216 4200
    5210002452 5290001782 F01U110220 2000
    5210002452 5290001828 F01U110220 2200
    5210007262 5290008282 F01U216216 1500
    5210001324 5290008811 F01U216216 1955

  88. Rahul says:

    I have 12 Person which needs to be allotted some task on each days. For ex. on Sunday 4 different person will be allotted different task, on Monday another 4 person will be allotted different task and so on.

    I have created a sheet as sunday, monday where each sheet have person name and their task.

    I have created a drop down list which displays days and on clicking Sunday, 4 person associated with Sunday is displayed. Now on clicking on the person A name, i want to display that particular person's task in a cell, on clicking On person B, result should be displayed on the same cell, replacing the older result.

    Please help.

  89. Leslie says:

    You are my hero. Thank you.

  90. Sheshank Pydikondala says:

    This is really awesome.. It helps me in my work. Thank you so much...

  91. REVA says:

    I have a normal drop down and a dependent drop down(dependent on first drop down) in one sheet, in adjacent sheet i have a table which have first two column with values same as both the drop downs.my first column is car model and next is engine type. one car model can have one or more engine type. So what i want is, if i select something from first dropdown(any car model) and any one engine type from second drop down(second drop down only shows the type of engines which a particular car has), the rest entries like cost of repair , interval of repair etc or simply all the next columns of the table should appear in the bottom. Also they should change automatically as soon as i change car model or engine type. please suggest a wayout.

  92. Tahsin Çetinkaya says:

    Thank you very much, Sohail

  93. Seshadri G says:

    Thanks, Sohail.. That is quite informative.. This site is very informative.. This is easier when all the data is either only in columns OR only in rows.. Here all the data is in columns.. Do we have a solution, if say, 2 of these data elements are in rows and the third data element is in column...? Say, we might have to apply a combi. of vlookup and hlookup..? is there a faster way to achieve this process using INDEX and MATCH..? Pls let us know.. Thanks

  94. BarBar says:

    Hi,
    What if I would like to lookup a value in column A OR in column B and not both of them? what should I do?

  95. Sree says:

    Wow, Another day another new learning. Thanks for this. Saves a whole bunch of time for me on my reporting needs.

  96. Kathy Westbrook says:

    Does this function still work? I am interested in using it in Google Sheets, but I can't get it working in either G Sheets or excel. I am using this formula:

    =vlookup(D2&" "&F2,CHOOSE({1,2},$D$2:$D$3122&" "&$F$2:$F$3122,$O2:$O3122),2,false)

    I'm expecting CHOOSE to build a 2 column result set consisting of col 1 = concatenated values of d and f, and col 2= values in o.

    The VLOOKUP evaluates to an out of bounds because it does not seem to acknowledge the second column (values from O) created by CHOOSE.

    Any ideas?

    • Hui... says:

      @Kathy

      It still works in Excel, I can't comment on G Sheets

      Your formula looks ok

      Don't forget in Excel you need to array enter the formula, that is press Ctrl+Shift+Enter

      If you still have problems,post a question in the Chandoo.org Forums https://chandoo.org/forum/
      and attach a sample file, as it maybe your data that is inconsistent, ie: Leading or trailing spaces or numbers that are text not numbers?

  97. DeVille says:

    That's a great tip! Thank u very much!

  98. Sheeloo says:

    Thanks Sohail - awsome. Thanks Chandoo - I am a big fan.

    Someone might have pointed this out ...

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

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

  99. Tom says:

    I am using the formula below to lookup a column containing a phone number.

    {=VLOOKUP(($D$8&" "&$D$7),CHOOSE({1,2},'Combo Of Orders'!D11:D2010&" "&'Combo Of Orders'!E11:E2010,'Combo Of Orders'!I11:I2010),2,0)}

    If the cell is blank, it is returning a 0 so I tried the following to test for a blank and return a blank if that is the case. This works in simple formulas, but it does not work here.

    {=IF(VLOOKUP(($D$8&" "&$D$7),CHOOSE({1,2},'Combo Of Orders'!D11:D2010&" "&'Combo Of Orders'!E11:E2010,'Combo Of Orders'!J11:J2010),2,0)="","",VLOOKUP(($D$8&" "&$D$7),CHOOSE({1,2},'Combo Of Orders'!D11:D2010&" "&'Combo Of Orders'!E11:E2010,'Combo Of Orders'!J11:J2010),2,0))}

  100. corey says:

    Thank you. I keep coming back to this periodically...

  101. corey says:

    ...more. Since trying to do a pivot table on text values in Excel doesn't work out so well, I used this today to great effect to get what I wanted.

    I wanted to pivot "normalized" data like this:
    flag code textstring
    0 '123' '54321'
    ...
    to:

    code 0
    '123' '12345' '54321'
    ...

    worked great! thank you.

  102. Ajay says:

    Dear Sir,
    Thanks. I can use the multi-condition-vlookup, but need to use "Ctrl + Shift + Enter". Please guide to use without "Ctrl + Shift + Enter".
    Thanking you.
    My email ID is ajaypune99@gmail.com

  103. BestPete says:

    I have noticed you don't monetize your website, don't waste your traffic, you can earn additional bucks every month.
    You can use the best adsense alternative for any type of website (they approve all websites), for more info simply search in gooogle: boorfe's tips monetize
    your website

  104. Marc says:

    Fantastic! I'm using it for a joke right now (a randomized, 25-spot management jargon bingo card), but I can't tell you how many times over the years I've wanted a two-criteria vlookup!

  105. Chantelle says:

    Good day

    Is it possible to match names that contains the same text in the same column. eg.

    Name Customer:
    Apple
    Apple123

    To return the name to Apple
    Thank you

  106. C. Wolfram says:

    Hello, I'm looking for a pretty difficult formula. Hopefully you can help me out. I have to look up a price of a bottle, but the bottle can be filled with different capacities. 40ml, 100ml, 200ml and 350ml. I tried a formula where I combined IF with VLOOKUP. The price for different volume fills have to be in one row, because there is more information about the product. So there is only one row with different prices. I like to search (lookup) with the order number and the capacity. The formula I tried is as follow:
    =IF(C29=$E$3,VLOOKUP(B29,$B$5:$AF$17,8,IF(C29=$L$3,VLOOKUP(B29,$B$5:$AF$17,15,IF(C29=$S$3,VLOOKUP($B$5:$AF$17,22,IF(C29=$Z$3,VLOOKUP(B29,$B$5:$AF$17,29))))))))
    As you can see it only works with the 1st IF and it shows the vlookup from column 8. Vlookup with column 15, 22 and 29 doesn't work, it shows "FALSE".

    Thank you in advance!
    Chris

  107. Mark Omachoko says:

    Thank you very much. I have learnt a lot from this site already. Keep the good job.

  108. Hi everyone

    Example of the problem:
    Column A Column B
    ABC CDE
    DBE AGB
    CDE ABC
    etc etc...

    I want to return the concatenation of Column A and Column B irrespective of the order.
    Therefore, ABC&"-"&CDE is the same as CDE&"-"&ABC and should only return ABC&"-"&CDE.

    I have about 90 000 rows that need to be matched as above.

    How can I do this?

  109. Hi!

    So I have a problem...

    Column A Column B
    ABC CDE
    FGH XMN
    HMN BLM
    CDE ABC
    etc etc...

    The above is just a sample of origin - destination pairs. There are a couple of thousand of pairs. The origin destination (ABC - CDE) can be visa versa as well (CDE - ABC), in which case I want it to return only ABC - CDE.

    How can I do this in excel?

  110. Allen Sungchang says:

    How to create a program same as below.

    Scenario:
    Code Description
    1818 ABC
    1819 DEF
    1818 GHI
    1819 JKL

    Program:
    Code Description
    1818 ABCGHI

    I need to merge in 1 cell the description if i type 1 unique code in excel.

    Kindly Help. Thanks Allens

    • Hui... says:

      @Allen

      You can use the Concatif() User Defined Function

      Use the Search Box at the top right of this screen and there is a post about it

  111. Hamish Waugh says:

    How would this approach work with a very large data set, i.e multiple hundreds of thousands of rows? I use a helper column and then a V lookup with a 'true' in it on a sorted list for a particular problem i have, any other method takes a literal age to calculate.

    • Jeff Weir says:

      Hamish: If you've got Excel 2016 or later, take your hands off the keyboard and step away from the VLOOKUP. Instead use PowerQuery to do a merge. Haven't heard of PowerQuery? It's the best thing since sliced PivotTables, and it's built into Excel 2016 or later. And it's available as a free add-in in Excel 2010 and Excel 2013.

      VLOOKUP is dead to me. PowerQuery is the new VLOOKUP.

  112. Harkesh Sharma says:

    Really great,
    Very well explained.
    So helpful.

  113. Munene says:

    Another way is the lookup formula
    =lookup(A7,A2:A5&" "&B2:B5,C2:C5)

Leave a Reply