Lookup & Reference
XLOOKUP with multiple criteria: match two or more columns using boolean math
Look up a rate or owner across two criteria columns using XLOOKUP(1, (Col1=Key1)*(Col2=Key2), ReturnCol) without adding a helper column.
Why boolean multiplication beats concatenating keys
Joining two columns with A2:A6 & B2:B6 can collide if ("AB" & "C") equals ("A" & "BC") unless you insert a delimiter, and helper columns clutter shared rate tables.
Multiplying (A2:A6=E2)*(B2:B6=F2) evaluates each condition separately: TRUE * TRUE becomes 1, while any non-matching column becomes 0. Searching for 1 with XLOOKUP returns the first row where every condition holds.
| Hub (A) | Tier (B) | Fee (C) | (A="West")*(B="Express") |
|---|---|---|---|
| East | Standard | 14 | 0 * 0 = 0 |
| East | Express | 29 | 0 * 1 = 0 |
| West | Standard | 18 | 1 * 0 = 0 |
| West | Express | 35 | 1 * 1 = 1 (match) |
| North | Standard | 16 | 0 * 0 = 0 |
Multi-criteria XLOOKUP formula
Include the fourth argument if_not_found ("No rate") so unoffered hub-and-tier combinations—such as North + Express—return an explicit message instead of #N/A.
=XLOOKUP(1, (A2:A6=E2)*(B2:B6=F2), C2:C6, "No rate")Adding numeric range criteria
Because each parenthesized test produces TRUE/FALSE, you can mix text equality and numeric inequalities such as (Weight<=MaxWeight) in the same product.
=XLOOKUP(1, (A2:A6=E2)*(B2:B6=F2), C2:C6)Try the example
Use the original values or change them to test your understanding.