In this Excel tutorial you will learn how to do a vlookup with multiple criteria in Excel.

Using CONCATENATE for Multiple Criteria VLOOKUP

Data Preparation

Begin with a dataset containing multiple columns, including the criteria you want to match.

Inserting a Concatenate Formula

Insert a new column by right-clicking on an existing one.

In the newly inserted column, create a concatenate formula using the CONCATENATE function (e.g., =CONCATENATE(B6, C6)) to combine the values of the criteria columns for each row.

Inserting Vlookup formula

In separate cells, enter the specific criteria you want to search for (e.g., “Tony” and “Olson”).

In an empty cell, use the VLOOKUP function to search for the concatenated criteria in your dataset. The formula should look like this: =VLOOKUP(B3&C3, $A$5:$E$10, 5, FALSE).

Replace “B3&C3” with the cell references containing your criteria.

Adjust the range and column number as needed based on your dataset.

Using INDEX MATCH for Multiple Criteria VLOOKUP

Data Preparation

Assign a named range to the data you want to search through.

In separate cells, enter the specific criteria you want to search for (e.g., “Tony” and “Olson”).

Applying the INDEX MATCH Formula

In an empty cell, use the INDEX and MATCH functions to perform the multiple criteria VLOOKUP. The formula should follow this structure:

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

• Replace “NamedRange” with the name you assigned to your data.
• Replace “FirstCriteria” with the value you’re searching for.
• Replace “ColumnForFirstCriteria” with the column containing the first criteria.
• Replace “SecondCriteria” with the value you’re searching for.
• Replace “ColumnForSecondCriteria” with the column containing the second criteria.

See also  How to Use Aggregate Function?

Related posts:

How to use Sum Function How to Calculate Irr in Excel How to use DATEDIF function