Quick Answer & Formula
Excel
Sheets
Intermediate
=XLOOKUP(1, (Criteria_Range1 = Val1) * (Criteria_Range2 = Val2), Return_Range, "Not Found") To XLOOKUP with multiple AND criteria, set the lookup_value to 1 and multiply the criteria ranges: =XLOOKUP(1, (ColA = "East") * (ColB = "Electronics") * (ColC = 2026), Return_Col, "Not Found").
How to XLOOKUP with Multiple Criteria (AND / OR Conditions)
Looking up a value based on two or more conditions (e.g. finding the price for a specific Product, Size, and Color) previously required concatenating helper columns. Modern XLOOKUP solves this with boolean arrays.
1. The Multi-Condition AND Formula
=XLOOKUP(1, (A2:A100 = Target_Dept) * (B2:B100 = Target_Role), C2:C100, "No Match")2. Practical Pricing Table Example
| Product (Col A) | Size (Col B) | Color (Col C) | Unit Price (Col D) |
|---|---|---|---|
| T-Shirt | Medium | Black | $22.00 |
| T-Shirt | Large | Black | $24.00 |
| T-Shirt | Large | Blue | $26.00 |
| Hoodie | Large | Black | $55.00 |
To find price for Hoodie + Large + Black:
=XLOOKUP(1, (A2:A5 = "Hoodie") * (B2:B5 = "Large") * (C2:C5 = "Black"), D2:D5, "N/A")- Returns:
$55.00
?
Frequently Asked Questions
Why does multiplying conditions work in XLOOKUP?
In Excel boolean logic, TRUE * TRUE = 1, and TRUE * FALSE = 0. Multiplying conditions produces an array of 1s and 0s. Searching for 1 finds the exact row where all conditions are TRUE.