Lookup & Reference
XLOOKUP with Complex AND + OR Criteria (Fixing the 1 + 1 = 2 Boolean Trap)
Combine AND (*) and OR (+) boolean array expressions inside XLOOKUP(1, ..., return_array) safely by clamping OR sums with > 0 so rows matching both OR conditions evaluate to 1 instead of 2.
The hidden 1 + 1 = 2 bug in boolean OR array lookups
To combine AND and OR criteria inside XLOOKUP(1, criteria_array, return_array), analysts multiply AND terms with * and add OR terms with +: for example, Region = "EU" AND (Tier = "Gold" OR Segment = "Enterprise").
Here is the silent bug: when a row satisfies BOTH Tier = "Gold" AND Segment = "Enterprise", (TRUE + TRUE) equals 2! Multiplying 1 * 2 produces 2—so XLOOKUP(1, ...) skips your best customer completely! Wrapping every OR sum in (... > 0) clamps any positive sum (1, 2, 3...) back to TRUE (1) before multiplying.
| Row | Region | Tier | Segment | Unclamped (A="EU")*((B="Gold")+(C="Ent")) | Clamped (... > 0) |
|---|---|---|---|---|---|
| 2 | EU | Gold | Enterprise | 1 * (1 + 1) = 2 (MISSED by XLOOKUP(1)!) | 1 * TRUE = 1 (Matched!) |
| 3 | US | Gold | SMB | 0 * (1 + 0) = 0 | 0 |
| 4 | EU | Silver | Enterprise | 1 * (0 + 1) = 1 (Wrongly picked if unclamped) | 1 |
| 5 | EU | Bronze | SMB | 1 * (0 + 0) = 0 | 0 |
Safe Clamped AND + OR Multi-Criteria XLOOKUP
Clamps the OR sum with > 0 so rows matching one OR branch and rows matching both OR branches both evaluate to 1.
=XLOOKUP(1, (A2:A5 = F2) * (((B2:B5 = F3) + (C2:C5 = F4)) > 0), D2:D5, "No Match")Filter All Rows Matching Complex AND + OR Criteria
The exact same clamped boolean product works directly inside FILTER because FILTER treats any non-zero number as TRUE.
=COUNTA(FILTER(D2:D5, (A2:A5 = F2) * (((B2:B5 = F3) + (C2:C5 = F4)) > 0)))Try the example
Use the original values or change them to test your understanding.