=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.