Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE

VLOOKUP using the Multiple Criteria Helper Column

Now we can go back to our standby VLOOKUP instead of resorting to INDEX/MATCH and those expensive array formulas… We’ll pair it with another CONCATENATE to do the multiple criteria part of the lookup. I’ve added a spot to specify the criteria next to the formula. These are just hard coded with “ILX” and “2.4” to look up that Acura model…

VLOOKUPCONCATENATEFormula

The formula is as follows:

=VLOOKUP(CONCATENATE(A2,B2),A5:N1159,9,FALSE)

The CONCATENATE(A2,B2) is the lookup_value and it specifies that we are looking for “ILX2.4”, which is the combined values of Model and Displacement for the Acura ILX 2.4 liter car.

The A5:N1159 is the range for the data table (called the table_array), starting with the first row and column in A5 and ending at the bottom right with cell N1159.

The number 9 is the col_index_num, which means we want the 9th column in the table_array – Column I in this example.

The FALSE input is the range_lookup field. FALSE means we want it to find an exact match and not just a close one.

You can change the criteria inputs to other car models and different displacements to find different results:

WorkingFormula

This method will work much faster when you are using it on big data sets or many times in one worksheet!

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 *