VLOOKUP with multiple critiera

VLOOKUP with multiple critiera

Generic formula 

=VLOOKUP(val1&val2,data,col_index,0)

Related formulas 

INDEX and MATCH with multiple criteria

VLOOKUP with 2 lookup tables

Two-way lookup with VLOOKUP

Calculate grades with VLOOKUP

Get employee information with VLOOKUP

Merge tables with VLOOKUP

VLOOKUP without #N/A error

Explanation

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

  1. Add a helper column and concatenate (join) values from columns you want to use for your criteria.
  2. 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.
  3. For the lookup value, join the same criteria in the same order to match values in the helper column.

VLOOKUP with multiple critiera

Generic formula 

=VLOOKUP(val1&val2,data,col_index,0)

Related formulas 

INDEX and MATCH with multiple criteria

VLOOKUP with 2 lookup tables

Two-way lookup with VLOOKUP

Calculate grades with VLOOKUP

Get employee information with VLOOKUP

Merge tables with VLOOKUP

VLOOKUP without #N/A error

Explanation

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

  1. Add a helper column and concatenate (join) values from columns you want to use for your criteria.
  2. 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.
  3. For the lookup value, join the same criteria in the same order to match values in the helper column.