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!