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:

=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:

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!

## Other posts in this series...

- How to VLOOKUP with Multiple Criteria Using INDEX and MATCH
- 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

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.