5 Ways To Vlookup With Multiple Criteria In Excel

When working with large datasets in Excel, it's common to need to look up values based on multiple criteria. While the VLOOKUP function is a powerful tool for searching for values in a table, it can be limited when trying to search for multiple criteria. Fortunately, there are several ways to VLOOKUP with multiple criteria in Excel.

In this article, we'll explore five methods for performing a VLOOKUP with multiple criteria, including using the INDEX-MATCH function, the FILTER function, and more. Whether you're a beginner or an advanced Excel user, you'll learn how to overcome the limitations of the traditional VLOOKUP function and perform more complex lookups.

Method 1: Using the INDEX-MATCH Function

One of the most popular alternatives to VLOOKUP is the INDEX-MATCH function. This method involves using the MATCH function to find the relative position of a value within a range, and then using the INDEX function to return a value from a corresponding range.

To perform a VLOOKUP with multiple criteria using INDEX-MATCH, follow these steps:

1. Enter the following formula: =INDEX(range,MATCH(1,(criteria1=range1)*(criteria2=range2),0))
2. Replace range with the range of cells you want to return a value from.
3. Replace criteria1 and criteria2 with the values you want to search for.
4. Replace range1 and range2 with the ranges of cells you want to search.

For example, suppose you have a table with the following columns: Employee ID, Name, Department, and Job Title. You can use the INDEX-MATCH function to find the Job Title of an employee based on their Employee ID and Department.

Formula: =INDEX(D:D,MATCH(1,(A:A=A2)*(C:C=C2),0))

Assuming the data is in cells A1:D10, and you want to find the Job Title of the employee with Employee ID 101 and Department Sales.

Benefits of Using INDEX-MATCH

• More flexible than VLOOKUP, as you can search for multiple criteria.
• Faster performance, as it uses absolute references instead of relative references.
• Can return values from any column, not just the adjacent column.

Method 2: Using the FILTER Function

The FILTER function is a new function in Excel that allows you to filter a range of cells based on a set of criteria. You can use this function to perform a VLOOKUP with multiple criteria.

To perform a VLOOKUP with multiple criteria using the FILTER function, follow these steps:

1. Enter the following formula: =FILTER(range,(criteria1=range1)*(criteria2=range2))
2. Replace range with the range of cells you want to return a value from.
3. Replace criteria1 and criteria2 with the values you want to search for.
4. Replace range1 and range2 with the ranges of cells you want to search.

For example, suppose you have a table with the following columns: Employee ID, Name, Department, and Job Title. You can use the FILTER function to find the Job Title of an employee based on their Employee ID and Department.

Formula: =FILTER(D:D,(A:A=A2)*(C:C=C2))

Assuming the data is in cells A1:D10, and you want to find the Job Title of the employee with Employee ID 101 and Department Sales.

Benefits of Using FILTER

• Easy to use and understand, as it uses a simple syntax.
• Fast performance, as it uses a efficient algorithm.
• Can return multiple values, not just a single value.

Method 3: Using Power Query

Power Query is a powerful tool in Excel that allows you to perform data manipulation and analysis. You can use Power Query to perform a VLOOKUP with multiple criteria.

To perform a VLOOKUP with multiple criteria using Power Query, follow these steps:

1. Go to the "Data" tab in the ribbon and click on "New Query".
2. Select the table you want to query and click on "OK".
3. In the Power Query Editor, click on the "Add Column" tab and select "Custom Column".
4. Enter the following formula: =if((criteria1=range1)*(criteria2=range2),value,"")
5. Replace criteria1 and criteria2 with the values you want to search for.
6. Replace range1 and range2 with the ranges of cells you want to search.
7. Replace value with the value you want to return.

For example, suppose you have a table with the following columns: Employee ID, Name, Department, and Job Title. You can use Power Query to find the Job Title of an employee based on their Employee ID and Department.

Formula: =if((A:A=A2)*(C:C=C2),D:D,"")

Assuming the data is in cells A1:D10, and you want to find the Job Title of the employee with Employee ID 101 and Department Sales.

Benefits of Using Power Query

• Powerful data manipulation and analysis capabilities.
• Fast performance, as it uses a efficient algorithm.
• Can return multiple values, not just a single value.

Method 4: Using the SUMIFS Function

The SUMIFS function is a function in Excel that allows you to sum values based on multiple criteria. You can use this function to perform a VLOOKUP with multiple criteria.

To perform a VLOOKUP with multiple criteria using the SUMIFS function, follow these steps:

1. Enter the following formula: =SUMIFS(range,criteria1,range1,criteria2,range2)
2. Replace range with the range of cells you want to return a value from.
3. Replace criteria1 and criteria2 with the values you want to search for.
4. Replace range1 and range2 with the ranges of cells you want to search.

For example, suppose you have a table with the following columns: Employee ID, Name, Department, and Job Title. You can use the SUMIFS function to find the Job Title of an employee based on their Employee ID and Department.

Formula: =SUMIFS(D:D,A:A,A2,C:C,C2)

Assuming the data is in cells A1:D10, and you want to find the Job Title of the employee with Employee ID 101 and Department Sales.

Benefits of Using SUMIFS

• Easy to use and understand, as it uses a simple syntax.
• Fast performance, as it uses a efficient algorithm.
• Can return multiple values, not just a single value.

Method 5: Using the XLOOKUP Function

The XLOOKUP function is a new function in Excel that allows you to perform a lookup based on a value. You can use this function to perform a VLOOKUP with multiple criteria.

To perform a VLOOKUP with multiple criteria using the XLOOKUP function, follow these steps:

1. Enter the following formula: =XLOOKUP(1,(criteria1=range1)*(criteria2=range2),range)
2. Replace criteria1 and criteria2 with the values you want to search for.
3. Replace range1 and range2 with the ranges of cells you want to search.
4. Replace range with the range of cells you want to return a value from.

For example, suppose you have a table with the following columns: Employee ID, Name, Department, and Job Title. You can use the XLOOKUP function to find the Job Title of an employee based on their Employee ID and Department.

Formula: =XLOOKUP(1,(A:A=A2)*(C:C=C2),D:D)

Assuming the data is in cells A1:D10, and you want to find the Job Title of the employee with Employee ID 101 and Department Sales.

Benefits of Using XLOOKUP

• Easy to use and understand, as it uses a simple syntax.
• Fast performance, as it uses a efficient algorithm.
• Can return multiple values, not just a single value.

Vlookup Multiple Criteria Image Gallery

We hope this article has helped you to understand how to perform a VLOOKUP with multiple criteria in Excel. Whether you choose to use the INDEX-MATCH function, the FILTER function, Power Query, the SUMIFS function, or the XLOOKUP function, you'll be able to perform more complex lookups and analyze your data with ease.