With 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:
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.
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:
You can drag the formula down the entire column, or copy and paste it down the sheet. The final result should look like this:
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 has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.
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...
- VLOOKUP with Multiple Values or Criteria Using INDEX and MATCH (How To)
- Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE
- Extract a List of Values Filtered by Criteria with Sub-Arrays
- How to Combine (Concatenate) Data from Multiple Rows into One Cell
- How to Unpivot Columns into Rows in Excel Using Power Query
13 thoughts on “Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE”
Thanks for this soultion – it is really fast. “INDEX and MATCH” took whole computational power for several minutes, even with 4 core procesor.
Really useful and helpful to fulfill my works. Thanx a lot.
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
Simplest way to solve a complex problem. Boss, so many thanks…
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.
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.
It’s wonderful tools.
Great alternative to INDEX MATCH! Thank you. INDEX MATCH was really bogging down my computer. This works much faster!!
This help huge thanks a million, something that took me days to do takes a few minutes now.
Thank you so much!!!!! It is super work for me!
How do you use this method to find multiple records that match the criteria?
I think you might like my article on sub-arrays.
Thanks for the article. That’s what I’d calll the brute-force solution. It has the drawback that one needs another helper column for each vlookup.
Is there also a version where you concatenate the two columns dynamically?