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!
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.
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...
- VLOOKUP with Multiple Values or Criteria Using INDEX and MATCH (How To)
- 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