XLOOKUP with Multiple Criteria: The Ultimate Guide

Microsoft Excel’s XLOOKUP function is a powerful upgrade to traditional lookup functions like VLOOKUP and INDEX-MATCH. It allows you to search for values in a table and return corresponding results efficiently. But what if you need to search using multiple criteria? In this guide, we’ll explore how to use XLOOKUP with multiple criteria effectively.

Chat with live technician– Click Here

Understanding XLOOKUP

The XLOOKUP function searches for a value in one column and returns a corresponding value from another column. The basic syntax is:

Where:

• lookup_value – The value you want to find.
• lookup_array – The range where Excel searches for the value.
• return_array – The range where Excel retrieves the result.
• if_not_found – A custom message if no match is found.
• match_mode – The type of match (exact, approximate, wildcard).
• search_mode – The order in which Excel searches (first to last or last to first).

How to Use XLOOKUP with Multiple Criteria

Since XLOOKUP does not directly support multiple criteria, we need to combine conditions using concatenation or array formulas.

Method 1: Concatenation Approach

By combining multiple criteria into one column, we can use XLOOKUP as usual.

Example:

Suppose you have a sales table where you need to find the price of a product based on Product Name and Category.

Step 1: Create a Helper Column

Concatenate Product and Category into a new column:

Step 2: Use XLOOKUP with the Helper Column

This formula looks for “LaptopElectronics” in the helper column and returns 1000 from the Price column.

Method 2: Using an Array Formula (Without Helper Column)

If you don’t want a helper column, you can use an array formula inside XLOOKUP.

Example:

Find the price of a Laptop in the Electronics category:

How It Works:

• (A2:A4="Laptop") creates an array {TRUE, FALSE, FALSE}.
• (B2:B4="Electronics") creates another array {TRUE, TRUE, FALSE}.
• Multiplying them results in {1, 0, 0}.
• XLOOKUP searches for 1 and returns the corresponding value from C2:C4.

Key Benefits of Using XLOOKUP with Multiple Criteria

✔ No need for complicated INDEX-MATCH formulas
✔ Works with both vertical and horizontal lookups
✔ Easier to understand than nested functions
✔ Supports exact, approximate, and wildcard matches

Conclusion

Using XLOOKUP with multiple criteria allows for more advanced data retrieval in Excel. Whether you use concatenation or array formulas, both methods provide efficient ways to search using multiple conditions. Mastering this technique will improve your ability to analyze and manage data in Excel.