Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE

FasterVLOOKUPLeadWith the combination of INDEX and MATCH functions and Excel’s powerful array formulas (entered using CTRL+SHIFT+ENTER), we can already make Excel do the hard work of looking up data with multiple criteria for us. I wrote about it in the article How to VLOOKUP with Multiple Criteria Using INDEX and MATCH, and you should definitely check it out first if you need to use multiple conditions when looking up data in Excel.

The only downside of the INDEX/MATCH method is that it takes a lot of crunching to get its results. If you just need to look through a couple hundred, it does a great job! If you’re working with truly big data sets numbering in the thousands or hundreds of thousands of rows, you might need something that can work faster… This tutorial will show you how to combine CONCATENATE and a helper column to make VLOOKUP faster than ever!

The formula INDEX/MATCH from the VLOOKUP with Multiple Criteria article is computationally intensive because it uses an array formula to compare all the values in both criteria columns to find what you are looking for. If you have thousands of rows, this means it has to check them all twice for every single lookup. If you put this array formula in multiple tables, you are doing the work of looking up the matches many, many times, and this can slow your computer to a crawl, make you run out of memory, or even crash Excel!

Making a Helper Column

The secret to speeding up the multiple criteria lookup is to split up the task of combining the criteria and doing the lookup. To do this, we need to add a column to the data set and combine the criteria we want to lookup. Let’s use the same example data set as the last tutorial:

CarData

Let’s try to recreate the same lookup as before. We want to be able to look up the Model (Column C) and the Displacement (Column D) of the car we want to be able to find out the Combined MPG (Column H). To do this, we need to insert a helper column that is there just for us to look up against. Right-click Column A and click Insert to add a column at the beginning of the sheet.

InsertHelperColumn

We are going to use CONCATENATE to combine the Model and Displacement information for each row in one column. The formula for the first row (in A2) is as follows:

CONCATENATEFormula

=CONCATENATE(D2,E2)

You can drag the formula down the entire column, or copy and paste it down the sheet. The final result should look like this:

HelperColumn

You can see that having both columns together in one makes each row have a unique identifier. “ILX1.5” is different than “ILX2.4” even though the Year, Make, and Model aren’t different for those rows. You can do this for any number of columns, which means you could use it for 2, 3, or 10 criteria and it wouldn’t require any extra computational work!

Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

Other posts in this series...

12 thoughts on “Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE

  1. Thanks for this soultion – it is really fast. “INDEX and MATCH” took whole computational power for several minutes, even with 4 core procesor.

  2. Hi, try getif() advanced that can be found in couple of VBA sites and enjoy treating arrays like sumif syntax and lots of possibilities. Can’t post code cause it gets erased but with a little search you’ll find it no problem

  3. I used this method while creating a calculator that allows users to determine the amount of money they can get with a reverse mortgage. It uses a table called PLF (principal limit factor) which is based on the borrower’s age and the loan interest rate. I concatenated the age and rate (for example a 65-year-old borrowing at a 3.125 percent rate gets a helper value of 653125. I have the VLOOKUP set to work with approximate values so that a rate of 3.124 percent would work (table is divided into 1/8th percent increments). The problem I have is that the lookup works most of the time but then with the same inputs comes up with crazy answers and I can’t figure out why. The table is sorted in ascending order, the helper values are numbers. I’m going crazy. Would really appreciate any help you can provide. Thank you.

    1. Vlookup usually gives crazy answers if you haven’t used the FALSE statement as the final argument.

      Whatever you have in your vlookup, enter ;FALSE to the end of it and it will look for exact matches.

Leave a Reply

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