How to Use XLOOKUP with Multiple Criteria in Excel (5 Shots)
XLOOKUP is powerful, but it is designed for single-criterion lookups by default. In real-world work, you often need multiple criteria such as Order ID + SKU. This article covers how to use XLOOKUP with multiple criteria in 5 Shots.
Summary
| Method | Key Point |
|---|---|
| Helper column method | Combine criteria into a single lookup key |
| TEXTJOIN method | Join criteria in real time |
| IF/FILTER combination | Overcome XLOOKUP’s single-criterion limitation |
| Practical examples | Find unit prices using Order ID + SKU |
| Extensions | Compare FILTER and INDEX-MATCH |
Shot 1 — Basic Principles
The basic XLOOKUP structure is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])Example: =XLOOKUP("A001", Orders[Order_ID], Orders[Qty])
Shot 2 — Implementing Multiple Criteria (Helper Column/Concatenation)
Combine two criteria to create a single lookup key:
=XLOOKUP(A2&B2, Orders[Order_ID]&Orders[SKU], Orders[UnitPrice])Or use TEXTJOIN:
=XLOOKUP(TEXTJOIN("|",,A2,B2), Orders[Key], Orders[UnitPrice])Shot 3 — Combining with IF/FILTER
To handle multiple criteria without a helper column, use FILTER:
=XLOOKUP(1, (Orders[Order_ID]=A2)*(Orders[SKU]=B2), Orders[UnitPrice])A match occurs when (criterion1)*(criterion2)=1. This is based on array operations.
Shot 4 — Practical Examples
- Look up order unit prices: Find the unit price using an Order ID + SKU combination
- Determine return status: Order ID + return code → return the return reason
- Find an inventory location: Warehouse code + product code → return the shelf number
=XLOOKUP(1, (Orders[Order_ID]=A2)*(Orders[SKU]=B2), Orders[UnitPrice])Shot 5 — Extensions & Alternatives
- FILTER: Can return rows that meet multiple criteria
- INDEX-MATCH: Useful for users familiar with implementing multiple criteria
- Performance: The helper column method is fastest for large tables
Related learning: Complete XLOOKUP Guide, Power Query Basics, Excel Printing/Reports