5 Ways Excel Lookup Multiple Values

Introduction to Excel Lookup Multiple Values

When working with Excel, one of the most common tasks is looking up values in a table or range. While Excel’s built-in functions like VLOOKUP and INDEX/MATCH are powerful, they are limited to looking up a single value. However, there are situations where you need to look up multiple values. In this article, we will explore five ways to lookup multiple values in Excel.

Method 1: Using the FILTER Function

The FILTER function is a new function introduced in Excel 2019 and later versions. It allows you to filter a range of data based on a condition. To lookup multiple values using the FILTER function, you can use the following syntax:

=FILTER(range, (condition1) * (condition2) * ... * (conditionN))

For example, suppose you have a table with the following data:

Name	Age	City
John	25	New York
Jane	30	Los Angeles
Bob	25	Chicago

To lookup all rows where the age is 25 and the city is New York, you can use the following formula:

=FILTER(A2:C4, (B2:B4=25) * (C2:C4="New York"))

This will return the following result:

Name	Age	City
John	25	New York

Method 2: Using the INDEX/MATCH Function

The INDEX/MATCH function is a powerful combination of functions that can be used to lookup multiple values. The syntax for the INDEX/MATCH function is:

=INDEX(range, MATCH(1, (condition1) * (condition2) * ... * (conditionN), 0))

For example, suppose you have the same table as before:

Name	Age	City
John	25	New York
Jane	30	Los Angeles
Bob	25	Chicago

To lookup all rows where the age is 25 and the city is New York, you can use the following formula:

=INDEX(A2:C4, MATCH(1, (B2:B4=25) * (C2:C4="New York"), 0))

This will return the following result:

Name	Age	City
John	25	New York

Method 3: Using the VLOOKUP Function with Multiple Criteria

The VLOOKUP function can be used to lookup multiple values by using the multiple criteria argument. The syntax for the VLOOKUP function with multiple criteria is:

=VLOOKUP(1, (condition1) * (condition2) * ... * (conditionN), range, FALSE)

For example, suppose you have the same table as before:

Name	Age	City
John	25	New York
Jane	30	Los Angeles
Bob	25	Chicago

To lookup all rows where the age is 25 and the city is New York, you can use the following formula:

=VLOOKUP(1, (B2:B4=25) * (C2:C4="New York"), A2:C4, FALSE)

This will return the following result:

Name	Age	City
John	25	New York

Method 4: Using the Power Query Function

The Power Query function is a powerful tool that can be used to lookup multiple values. To use the Power Query function, you need to: * Go to the Data tab in the ribbon * Click on From Table/Range * Select the table or range that you want to lookup * Click on OK * In the Query Editor, click on Add Column * Select Custom Column * Enter the following formula: = Table.Filter(#"Previous Step", each ([Age] = 25 and [City] = "New York")) * Click on OK This will return the following result:

Name	Age	City
John	25	New York

Method 5: Using the Advanced Filter Function

The Advanced Filter function is a powerful tool that can be used to lookup multiple values. To use the Advanced Filter function, you need to: * Go to the Data tab in the ribbon * Click on Advanced * Select the table or range that you want to lookup * Click on OK * In the Advanced Filter dialog box, select the Filter the list, in-place option * Enter the following formula: =AND(B2:B4=25, C2:C4="New York") * Click on OK This will return the following result:

Name	Age	City
John	25	New York

📝 Note: The Advanced Filter function will filter the data in-place, meaning that it will change the original data. Make sure to make a copy of the data before using this function.

In summary, there are five ways to lookup multiple values in Excel: using the FILTER function, the INDEX/MATCH function, the VLOOKUP function with multiple criteria, the Power Query function, and the Advanced Filter function. Each method has its own strengths and weaknesses, and the choice of method will depend on the specific needs of your project.