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…
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:
This method will work much faster when you are using it on big data sets or many times in one worksheet!
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?