Lookup & Reference
XLOOKUP with Logical & Inequality Criteria: XLOOKUP(1, (Cond1)*(Cond2), ...)
Search for the first or last row satisfying combined equality, greater-than (>), and range-bound conditions using XLOOKUP(1, (ColA = X) * (ColB >= Y), ReturnCol, "No Match").
Unlock >, <, <> and mixed AND/OR conditions inside XLOOKUP!
Concatenating keys like E2 & "|" & F2 works great when every condition is an exact "=" match—but concatenation CANNOT express inequalities like "Region = West AND Stock >= 50 AND UnitCost <= $25"! That is where the Boolean-1 Array Pattern shines: =XLOOKUP(1, (B2:B6 = F2) * (C2:C6 >= G2) * (D2:D6 <= H2), A2:A6, "No Qualified Supplier")!
Why does multiplying (Cond1) * (Cond2) * (Cond3) work so cleanly? In array math, TRUE * TRUE * TRUE evaluates to 1 * 1 * 1 = 1, while ANY false condition multiplies by 0! Searching for lookup_value = 1 inside that 0/1 array finds the exact first qualifying row (or the LAST qualifying row when 6th argument search_mode = -1)!
| Supplier Row | Region = "West"? | Stock >= 50? | Price <= $25? -> Product | Match? |
|---|---|---|---|---|
| V-101 ("East", 80, $22) | 0 (FALSE) | 1 (TRUE) | 1 -> 0*1*1 = 0 | Skipped |
| V-102 ("West", 30, $20) | 1 (TRUE) | 0 (FALSE) | 1 -> 1*0*1 = 0 | Skipped (Low stock) |
| V-103 ("West", 120, $24) | 1 (TRUE) | 1 (TRUE) | 1 -> 1*1*1 = 1 | 1st Match! ("V-103") |
| V-105 ("West", 90, $19) | 1 (TRUE) | 1 (TRUE) | 1 -> 1*1*1 = 1 | Last Match (search_mode = -1) |
First Row Matching Mixed Equality + Inequality Criteria
Finds the first supplier in A2:A6 matching Region F2 with Stock >= G2 and UnitPrice <= H2.
=XLOOKUP(1, (B2:B6 = F2) * (C2:C6 >= G2) * (D2:D6 <= H2), A2:A6, "No Qualified Supplier")Last Qualifying Row via Reverse Boolean Search (search_mode = -1)
Sets 6th argument search_mode = -1 to scan bottom-up and return the latest qualifying entry.
=XLOOKUP(1, (B2:B6 = F2) * (C2:C6 >= G2) * (D2:D6 <= H2), A2:A6, "No Qualified Supplier", 0, -1)Try the example
Use the original values or change them to test your understanding.