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

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
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.

26 Responses to “FIFA Worldcup Excel Spreadsheets [Roundup]”

  1. J. J. says:

    Nice roundup! Do you know of any one-page spreadsheets which will be updated by an administrator after each game? Would be nice to be able to print out the latest results whenever I feel like checking them as I probably won't be following closely every day.

    I actually haven't tried any of the above ones yet, but I thought I'd mention this one that I found which makes a nice one-page form you can fill in dynamically. http://exceltemplate.net/sports/world-cup-2010-schedule-and-scoresheet/

  2. Diego Garcia says:

    I would like to recommend you these one: http://www.anotagol.com/
    You can choose your interface language (english, spanish, italian, portuguese, german or french) and your country for the timezone of match. I like it very much.

  3. [...] Passend zu gerade laufenden Fußball-WM gibt es auf Chandoo.org alles wissenswerte über Excel-Anwendungen für den Fußball-Fan. [...]

  4. Rohit1409 says:

    Great!!!

    I strongly recommend this :

    http://www.en.excel-soccer-2010.de/downloads

    Chandoo how you found this ...

  5. Chandoo says:

    @Rohit.. really beautiful file. I missed it during my research. Now, I recommend it. 🙂

  6. Mourad Louha says:

    Hi Chandoo - thanks for the recommandation 🙂 - Regards

  7. [...] Excel, then print it on the other side of your Match Schedule from step 2 above. There are several other Excel spreadsheet templates you can download, but this is probably the only one-page version you can find; plus, it [...]

  8. Robert says:

    Does anybody know how to re-create this(?): http://www.marca.com/deporte/futbol/mundial/sudafrica-2010/calendario-english.html
    ...or do you know where a template can be found? I am DYING to have something like this on my site. When I found it, I had been looking for the longest time for a circular calendar. I found a couple that weren't adequate. Then I stumbled upon this one and my eyes nearly popped out of my head. If anyone can lead me in the right direction, I would be eternally grateful!

    Thanks in advance!
    Robert

  9. Chandoo says:

    @Robert...

    Doing something like that is a lot of work. You can probably get it done with some hired help from a flash developer.

  10. Pedro Wave says:

    @Robert, the World Cup flash in the Spanish Marca newspaper is impresive, but not much as my own animated spreadsheet with the Goals of 2010 World Cup South Africa in Excel that I just published into my blog:
    http://pedrowave.blogspot.com/2010/06/goals-of-2010-world-cup-south-africa-in.html

    Download from here:
    http://cid-6b219f16da7128e3.office.live.com/view.aspx/.Public/Goals%20South%20Africa%20Animated.xlsx

    And start to enter the goals of the rest of matches.

  11. Neil says:

    Has anyone seen, or made, a Spreadsheet where you can record the scorers and see a 'top scorers' chart. Would be a nice enhancement

  12. Chandoo says:

    @Neil... checkout this one http://www.inflexionary.com/sports/world-cup-2010-excel

    it uses macros to fetch scores from web (and provides very comprehensive analysis too)

    @All.. Thanks for the comments. I have updated the post with few more links now.

  13. Sergio Mathias says:

    Hi,
    Check this dashboards too:
    http://dashboards.org/world-cup-dashboards-and-visualizations/
    😉

  14. [...] Here is a collection of FIFA World Cup Spreadsheets if you are more in to that sort of thing. | [...]

  15. [...] Cup fever is here!In FIFA Worldcup Excel Spreadsheets Roundup, Chandoo has some links to useful World Cup tracking workbooks. Only one of them (the first one) [...]

  16. [...] World Cup fever is here!In FIFA Worldcup Excel Spreadsheets Roundup, Chandoo has some links to useful World Cup tracking workbooks. Only one of them (the first one) [...]

  17. Hey, you missed ours! It has everything you need and more, but not a whole pile of silly extras (National Anthems, etc).  I'll be making another one for the 2014 world cup.  We had over 4000 hits on it!

  18. Neil says:

    @Michael Harwood.

    Where is it then? You should have posted a link  

  19. phillip says:

    Sie sollten an einem Wettbewerb teil zu nehmen für einen der besten Blogs im Web. Ich werde empfehlen Sie diese Seite!
    Google translation: You should take part in a contest for one of the best blogs on the web. I will recommend this site!

  20. [...] and welcome to the forum, Maybe these similar spreadsheets might give you a few initial ideas: FIFA Worldcup Excel Spreadsheets [Roundup] | Chandoo.org - Learn Microsoft Excel Online If you have specific areas / formulae / layout choices for parts of your spreadsheet that you are [...]

  21. Petros says:

    Calling all football fans around the globe! The biggest football festival will kick off on the 12th June 2014 and everyone is placing their bets of who will have the honour of lifting the golden trophy.

    Use our free interactive Excel templatel to predict the World cup finalists ! No macros !

    http://www.spreadsheet1.com/world-cup-2014-free-excel-prediction-template.html

  22. marten says:

    I also made a Worldcup-tracker, with MS Access, which can also generate reports in Excel
    e.g. a match-schedule with locations on y-axis and dates on x-axis, see:
    http://worktimesheet2014.blogspot.com.es/2014/05/excel-with-match-schedule-for-2014-fifa.html
    and:
    http://worktimesheet2014.blogspot.com.es/2014/05/match-access-app-to-track-world-cup.html

  23. Vivek Ranjan says:

    where can i find raw data in excel file format of fifa world cups (1930-2014)

Leave a Reply