How to do a Vlookup with Multiple Criteria

In this Excel tutorial you will learn how to do a vlookup with multiple criteria in Excel. When doing the VLOOKUP with multiple conditions, different things matter.

There will be different things that make it possible to do the lookup.

But, this start with having a data like this:

Inserting a concatenate formula

1. Click on column A, Right Click on it (1), and choose Insert (2).

2. Click on the new column, right beside Adam (1), type =CONCATENATE(B6;C6) (2), and double click on the small square that is on right lower corner of the result (3).

Inserting Vlookup formula

3. Write the two Criteria (in this case, name) in respective cells. Note: The Tony and Olson written in their respective cells are the two criteria that we will use the VLOOKUP function to find.

4. Click on an empty cell, and then type in =VLOOKUP(B3&C3;$A$5:$E$10;5;FALSE).

Inserting INDEX MATCH

A Vlookup with multiple criteria can be also achieved using the INDEX and MATCH functions in Microsoft Excel. Here's a simple example:

1. Assign a named range to the data you want to search through.
2. In a separate column, use the MATCH function to find the first criteria you want to search for.
3. Use another MATCH function to find the second criteria.
4. Combine the two MATCH functions in an INDEX function to return the result you're looking for.

Here's the formula for this example:

=INDEX(NamedRange,MATCH(FirstCriteria,ColumnForFirstCriteria,0),MATCH(SecondCriteria,ColumnForSecondCriteria,0))

Note: Replace "NamedRange" with the name you assigned to your data, "FirstCriteria" with the value you're searching for, "ColumnForFirstCriteria" with the column containing the first criteria, and "SecondCriteria" with the value you're searching for and "ColumnForSecondCriteria" with the column containing the second criteria.