The XLOOKUP function in Excel can look up a value and return a matching result from another column or row. When you combine XLOOKUP with multiple criteria, you can search for a result based on more than one condition, such as matching both a customer name and a product, or both a region and a sales rep. This is especially useful in business reports, sales tracking, inventory lists, payroll records, and lookup tables where a single condition is not specific enough.
In This Guide, You’ll Learn
- What XLOOKUP with multiple criteria does
- How the formula works
- How to combine lookup conditions
- Practical examples using business data
- Common mistakes to avoid
- Real-world uses for XLOOKUP with multiple criteria
What is the XLOOKUP Function?
The XLOOKUP function in Excel searches for a value in a lookup range and returns a related value from another range.
XLOOKUP Function Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Argument Definitions
- lookup_value: The value you want Excel to search for.
- lookup_array: The range where Excel should look for the value.
- return_array: The range where Excel should return the result from.
- if_not_found: Optional. The value to return if no match is found.
- match_mode: Optional. Controls exact or approximate matching.
- search_mode: Optional. Controls the search direction.
How to Use XLOOKUP with Multiple Criteria in Excel
To use XLOOKUP with multiple criteria, combine the criteria with the ampersand symbol.
The basic pattern is:
=XLOOKUP(criteria1&criteria2, range1&range2, return_range)This tells Excel to match both conditions.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> XLOOKUP with Multiple Criteria Function Data
Find Revenue by Sales Rep and Product
In this example, we want to return the revenue for a specific sales rep and product.
Formula Used
=XLOOKUP("Baker"&"Laptop",A2:A6&B2:B6,C2:C6)
How This Formula Works
- The formula combines the sales rep and the product.
- Excel then looks for the matching combination and returns the revenue from column C.
Use Cell References for Multiple Criteria
Instead of typing the criteria directly into the formula, you can use cell references.
Formula Used
=XLOOKUP(E2&F2,A2:A6&B2:B6,C2:C6)This is usually better than hardcoding values because you can change the lookup inputs without rewriting the formula.
Find Inventory Quantity by Item and Warehouse
XLOOKUP with multiple criteria is also helpful for inventory tracking.
Formula Used
=XLOOKUP(E2&F2,A2:A6&B2:B6,C2:C6)How This Formula Works
- The formula finds the exact item-warehouse combination and returns the correct quantity.
Add an If Not Found Message
You can add a custom message when no matching result is found.
Formula Used
=XLOOKUP("Evans"&"West",A2:A6&B2:B6,C2:C6,"Not found")
How This Formula Works
- Excel looks for Evans in column A and West in column B
- Since it can’t find West in column B, it returns “Not found”
Common Mistakes When Using XLOOKUP with Multiple Criteria
One common mistake is forgetting that the lookup value and lookup array must be built the same way. If you combine the sales rep and product in the lookup value, you also need to combine the sales rep range and product range in the lookup array.
Another mistake is using ranges of different sizes. For example, A2:A10 and B2:B20 should not be combined in the same XLOOKUP formula.
Also, watch for extra spaces. “Adams” and “Adams ” may look similar, but Excel treats them as different values.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> XLOOKUP with Multiple Criteria Function Data
