If you already use Microsoft Excel, you know how powerful lookup functions can be. But when your data grows and you need to match more than one condition, things get more complex. That is where XLOOKUP with multiple criteria becomes a game changer.
This guide explains exactly how to use XLOOKUP with multiple conditions, with clear examples you can apply immediately.
Why Use XLOOKUP with Multiple Criteria?
Standard lookups match only one value. In real-world spreadsheets, that is rarely enough.
For example, you might need to:
- Find a product price based on Product Name + Region
- Return a salary based on Employee Name + Department
- Match an order using Customer ID + Date
Instead of building complicated helper columns, XLOOKUP allows you to combine criteria directly.
Basic Syntax of XLOOKUP
Before going advanced, here is the core structure:
=XLOOKUP(lookup_value, lookup_array, return_array)
To handle multiple criteria, we modify the lookup_value and lookup_array.
Method 1: Using Boolean Logic (Best Approach)
This is the most efficient and commonly used method.
Example Data:
| Name | Department | Salary |
|---|---|---|
| John | IT | 1000 |
| Anna | HR | 1200 |
| John | HR | 1100 |
Goal:
Find salary where:
- Name = John
- Department = HR
Formula:
=XLOOKUP(1, (A2:A4="John")*(B2:B4="HR"), C2:C4)
How It Works:
(A2:A4="John")returns TRUE/FALSE(B2:B4="HR")returns TRUE/FALSE- Multiplying them converts to:
- TRUE * TRUE = 1
- Others = 0
- XLOOKUP searches for 1, which represents a full match
This avoids helper columns and keeps your sheet clean.
Method 2: Using Concatenation
Another way is combining values into one string.
Formula:
=XLOOKUP("John"&"HR", A2:A4&B2:B4, C2:C4)
Important Note:
- This method works but is less reliable
- It may cause issues if values overlap (e.g., “AB” + “C” vs “A” + “BC”)
Use it only for simple datasets.
Method 3: Using Named Cells (Dynamic Criteria)
If your criteria are in cells (recommended for real use):
Assume:
- E1 = Name
- F1 = Department
Formula:
=XLOOKUP(1, (A2:A100=E1)*(B2:B100=F1), C2:C100)
This makes your spreadsheet interactive and scalable.
Advanced Tips for Better Results
1. Handle Errors Gracefully
Avoid ugly errors with:
=XLOOKUP(1, (A2:A100=E1)*(B2:B100=F1), C2:C100, "Not Found")
2. Use Exact Match Only
XLOOKUP defaults to exact match, which is perfect for multi-criteria lookups. No need to change match mode.
3. Improve Performance
For large datasets:
- Avoid full column references like A:A
- Use structured tables if possible
- Keep ranges consistent in size
Common Mistakes to Avoid
- Mixing text and numbers in criteria
- Using mismatched range sizes
- Forgetting parentheses in logical tests
- Using concatenation where boolean logic is safer
When Should You Use This Technique?
Use XLOOKUP with multiple criteria when:
- You want a clean formula without helper columns
- Your dataset requires precise filtering
- You are replacing older functions like VLOOKUP or INDEX/MATCH
XLOOKUP vs Older Methods
Compared to older solutions:
- Cleaner than INDEX + MATCH combinations
- More readable than array formulas
- Easier to maintain and debug
If you are still using VLOOKUP, this is a major upgrade.
