=VLOOKUP(val1&val2,data,col_index,0)
One of the limitations of VLOOKUP is that it only handles one condition: the lookup_value, which is matched against the first column in the table.
This makes it difficult to use VLOOKUP to find a value in a table based on two or more columns in a table. However, if you have control over the source data, there’s a simple workaround you can use to allow VLOOKUP to handle multiple criteria.
The trick is to add a helper column that concatenates 2 more more values together, then give VLOOKUP a lookup value that does the same.
In the example, we want to lookup employee department and group using VLOOKUP, but first and last names appear separately, so we don’t have a direct way of using both first and last name in the lookup. The solution is to use a helper column to combine them, then use the helper column to perform the lookup.
The VLOOKUP formula itslef looks like this, where data is a named range that includes the data portion of the table
=VLOOKUP(C3&D3,data,4,0)
In column B, the helper column, the a formula joins first name and last name together using concatenation:
=C6&D6
The First name comes from column C and the Last name from column D.
How this formula works
The helper column joins values from columns that are used as criteria. It must be the first column of the table, and functions as a “key”. Inside the VLOOKUP function, the lookup value itself is also created by joining the same criteria.
Setting things up
To set up a multiple criteria VLOOKUP, follow these 3 steps:
- Add a helper column and concatenate (join) values from columns you want to use for your criteria.
- Set up a VLOOKUP formula that refers to a table that includes the helper column. The helper column must be the first column in the table.
- For the lookup value, join the same criteria in the same order to match values in the helper column.
