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