Multiple criteria lookups in Excel are powerful tools for data analysis and management. They allow you to retrieve specific information from large datasets based on multiple conditions, making your work more efficient and accurate. In this comprehensive guide, we will explore over 20 practical examples of multiple criteria lookups, covering various scenarios and functions like VLOOKUP, HLOOKUP, INDEX-MATCH, and more. Whether you're a beginner or an advanced Excel user, this guide will help you master these techniques and enhance your data manipulation skills.

VLOOKUP with Multiple Criteria

VLOOKUP is a versatile function for vertical lookups in Excel. By combining it with other functions and techniques, you can perform multiple criteria lookups. Here are some examples:

VLOOKUP with AND Condition

You can use the AND function to create a condition that checks multiple criteria. For instance, to find a specific product based on its category and price, you can use the following formula:

=VLOOKUP(AND(B2="Electronics", C2>100), A2:D10, 2, FALSE)

This formula searches for the category "Electronics" and a price greater than $100 in the range A2:D10 and returns the corresponding product name.

VLOOKUP with OR Condition

Similarly, you can use the OR function to create a condition that matches any of the specified criteria. For example, to find products that are either "Electronics" or "Clothing" in category, you can use:

=VLOOKUP(OR(B2="Electronics", B2="Clothing"), A2:D10, 2, FALSE)

This formula returns the product names for the specified categories.

VLOOKUP with Multiple Criteria and INDEX-MATCH

Combining VLOOKUP with INDEX-MATCH can enhance your multiple criteria lookups. For instance, to find the price of a product based on its name and category, you can use:

=VLOOKUP(INDEX(A2:A10, MATCH(B2, B2:B10, 0)), C2:D10, 2, FALSE)

This formula first uses INDEX-MATCH to find the row number of the product name and then performs a VLOOKUP to retrieve the price.

HLOOKUP with Multiple Criteria

HLOOKUP performs horizontal lookups in Excel. Similar to VLOOKUP, you can create multiple criteria HLOOKUPs:

HLOOKUP with AND Condition

To find a specific value based on two conditions in a horizontal range, you can use the AND function. For example, to find the sales data for a specific product and month, you can use:

=HLOOKUP(AND(B2="Product A", C2="January"), A2:E10, 2, FALSE)

This formula searches for "Product A" and "January" in the range A2:E10 and returns the corresponding sales data.

HLOOKUP with OR Condition

You can also use the OR function to create a condition that matches any of the specified criteria. For instance, to find the sales data for either "Product A" or "Product B," you can use:

=HLOOKUP(OR(B2="Product A", B2="Product B"), A2:E10, 2, FALSE)

This formula returns the sales data for the specified products.

HLOOKUP with Multiple Criteria and INDEX-MATCH

Combining HLOOKUP with INDEX-MATCH can be useful when dealing with large datasets. For example, to find the sales data for a specific product and month, you can use:

=HLOOKUP(INDEX(A2:A10, MATCH(B2, B2:B10, 0)), C2:E10, 2, FALSE)

This formula first uses INDEX-MATCH to find the column number of the product name and then performs an HLOOKUP to retrieve the sales data.

INDEX-MATCH with Multiple Criteria

INDEX-MATCH is a powerful combination of functions that allows for versatile lookups. Here are some examples of multiple criteria INDEX-MATCH lookups:

INDEX-MATCH with Multiple Criteria in One Argument

You can use multiple criteria in the MATCH function to perform a lookup based on several conditions. For instance, to find the price of a product based on its name and category, you can use:

=INDEX(D2:D10, MATCH(AND(B2="Product A", C2="Electronics"), A2:C10, 0))

This formula searches for "Product A" and "Electronics" in the range A2:C10 and returns the corresponding price.

INDEX-MATCH with Multiple Criteria in Different Arguments

You can also use multiple criteria in different arguments of the INDEX-MATCH function. For example, to find the sales data for a specific product and month, you can use:

=INDEX(E2:E10, MATCH(B2, A2:A10, 0), MATCH(C2, B2:B10, 0))

This formula first finds the row number of the product name and then the column number of the month to retrieve the sales data.

INDEX-MATCH with Multiple Criteria and Wildcards

Wildcards can be used with INDEX-MATCH to perform more flexible lookups. For instance, to find the sales data for products containing the word "apple" in their names, you can use:

=INDEX(E2:E10, MATCH("*apple*", A2:A10, 0))

This formula uses the wildcard "*" to search for any product name containing the word "apple."

Using Tables for Multiple Criteria Lookups

Excel tables are powerful tools for managing and analyzing data. They provide dynamic ranges and can be used for multiple criteria lookups. Here's an example:

Multiple Criteria Lookup in an Excel Table

Create an Excel table with headers like "Product," "Category," and "Price." Then, you can use the following formula to find the price of a product based on its name and category:

=VLOOKUP(B2&C2, Table1[#All], 3, FALSE)

This formula combines the product name and category in cell B2 and C2, and then performs a VLOOKUP in the Excel table "Table1" to retrieve the price.

Multiple Criteria Lookups with Array Formulas

Array formulas can be used to perform multiple criteria lookups and return results as an array. Here's an example:

Array Formula for Multiple Criteria Lookup

To find the prices of products based on their categories, you can use the following array formula:

=IFERROR(INDEX(D2:D10, MATCH(B2, A2:A10, 0)), "Not Found")

This formula uses INDEX-MATCH to find the prices of products in the specified category. If a match is not found, it returns "Not Found."

Using Power Query for Multiple Criteria Lookups

Power Query is a powerful data transformation and loading tool in Excel. It can be used to perform multiple criteria lookups and transform data. Here's an example:

Power Query for Multiple Criteria Lookup

With Power Query, you can create a query that filters data based on multiple criteria. For instance, to find products with a price greater than $100 and a category of "Electronics," you can create a query with the following steps:

1. Load your data into Power Query.
2. Add a conditional column to filter products based on price.
3. Add another conditional column to filter products based on category.
4. Apply the filters and load the data back into Excel.

This approach allows you to dynamically filter and load data based on multiple criteria.

VLOOKUP with Multiple Criteria and Text Functions

Combining VLOOKUP with text functions like FIND, SEARCH, and LEFT can enhance your multiple criteria lookups. Here's an example:

VLOOKUP with Text Functions

To find the price of a product based on its name and category, you can use the following formula:

=VLOOKUP(B2&C2, A2:D10, FIND(C2, B2&C2)-LEN(C2)+2, FALSE)

This formula concatenates the product name and category in cell B2 and C2, and then uses the FIND function to locate the category within the concatenated text. It then adjusts the column number to retrieve the price.

HLOOKUP with Multiple Criteria and Text Functions

Similar to VLOOKUP, you can combine HLOOKUP with text functions for multiple criteria lookups. Here's an example:

HLOOKUP with Text Functions

To find the sales data for a specific product and month, you can use the following formula:

=HLOOKUP(B2&C2, A2:E10, SEARCH(C2, B2&C2)-LEN(C2)+2, FALSE)

This formula concatenates the product name and month in cell B2 and C2, and then uses the SEARCH function to locate the month within the concatenated text. It adjusts the row number to retrieve the sales data.

Nested IF Functions for Multiple Criteria Lookups

Nested IF functions can be used to perform multiple criteria lookups. Here's an example:

Nested IF for Multiple Criteria Lookup

To find the sales data for a specific product and month, you can use the following nested IF formula:

=IF(B2="Product A" AND C2="January", E2, IF(B2="Product B" AND C2="February", E3, "Not Found"))

This formula checks if the product name is "Product A" and the month is "January." If true, it returns the sales data from cell E2. If not, it checks for "Product B" and "February," and returns the sales data from cell E3. If neither condition is met, it returns "Not Found."

SUMIFS and COUNTIFS for Multiple Criteria Lookups

SUMIFS and COUNTIFS functions can be used to perform multiple criteria lookups and calculate sums or counts based on those criteria. Here's an example:

SUMIFS and COUNTIFS for Multiple Criteria Lookup

To find the total sales for products with a price greater than $100 and a category of "Electronics," you can use the following formulas:

=SUMIFS(E2:E10, A2:A10, "Electronics", C2:C10, ">100")
=COUNTIFS(A2:A10, "Electronics", C2:C10, ">100")

The SUMIFS formula calculates the sum of sales data for the specified criteria, while the COUNTIFS formula counts the number of products that meet the criteria.

Using CHOOSE and INDEX Functions for Multiple Criteria Lookups

The CHOOSE and INDEX functions can be combined to perform multiple criteria lookups. Here's an example:

CHOOSE and INDEX for Multiple Criteria Lookup

To find the price of a product based on its name and category, you can use the following formula:

=CHOOSE(MATCH(B2&C2, A2:A10&B2:B10, 0), D2:D10)

This formula uses MATCH to find the row number of the product name and category, and then uses CHOOSE to select the corresponding price from the range D2:D10.

CONCATENATE Function for Multiple Criteria Lookups

The CONCATENATE function can be used to combine multiple criteria into a single string for lookups. Here's an example:

CONCATENATE for Multiple Criteria Lookup

To find the price of a product based on its name and category, you can use the following formula:

=VLOOKUP(CONCATENATE(B2, C2), A2:D10, 2, FALSE)

This formula uses the CONCATENATE function to combine the product name and category in cell B2 and C2, and then performs a VLOOKUP to retrieve the price.

LOOKUP Function for Multiple Criteria Lookups

The LOOKUP function can be used for multiple criteria lookups, especially when combined with other functions. Here's an example:

LOOKUP for Multiple Criteria Lookup

To find the sales data for a specific product and month, you can use the following formula:

=LOOKUP(2, 1/(B2:B10=B2), E2:E10)

This formula uses the LOOKUP function to find the sales data for the specified product by comparing the product names in the range B2:B10 with the given product name in cell B2.

Using Database Functions for Multiple Criteria Lookups

Excel's database functions, such as DGET, DSUM, DCOUNT, and others, can be used for multiple criteria lookups on databases. Here's an example:

DGET for Multiple Criteria Lookup

To find the price of a product based on its name and category, you can use the DGET function as follows:

=DGET(A2:D10, A2:A10=B2, C2:C10=C2, 3)

This formula uses the DGET function to retrieve the price of the product based on its name and category from the database range A2:D10.

Conclusion

In this comprehensive guide, we explored various techniques for performing multiple criteria lookups in Excel. From VLOOKUP and HLOOKUP to INDEX-MATCH, array formulas, and Power Query, you now have a range of tools to efficiently retrieve specific information from large datasets. By combining these functions and techniques, you can create powerful and flexible lookups to enhance your data analysis and management capabilities. Remember to practice and adapt these examples to your specific needs, and you'll soon become a master of multiple criteria lookups in Excel.

FAQ