XLOOKUP with multiple criteria — three patterns that actually work.
XLOOKUP doesn't take multi-criteria as a built-in argument — but the same answer pops out of three different patterns. The & concatenation trick is the easiest and most readable. Boolean multiplication is the dynamic-array native version. FILTER replaces XLOOKUP entirely when you want all matching rows, not just the first. Below: an interactive demo showing why single-key XLOOKUP returns the wrong row, three working patterns with copy-paste formulas, and a decision tree for picking the right one.
| A — Name | B — Region | C — Quarter | D — Sales | |
|---|---|---|---|---|
| 2 | Carol | Design | Q1 | 45,000 |
| 3 | Carol | Eng | Q2 | 38,000 |
| 4 | Carol | Design | Q3 | 52,000MATCH |
| 5 | Bob | Design | Q1 | 41,000 |
| 6 | Bob | Eng | Q3 | 39,000 |
| 7 | XLOOKUP RESULT — "Carol" & "Q3" matches row 4 (Carol · Design · Q3) | 52,000 | ||
=XLOOKUP("Carol", A2:A6, D2:D6) would return 45,000 — Carol's first row (Q1), not Q3. Three Carols in the table, three different quarters. Single-key XLOOKUP picks the topmost — usually the wrong row.| Pattern | Formula shape | Best for |
|---|---|---|
| & concatenation | =XLOOKUP(A & B, names & quarters, sales) | Most cases — readable, robust, easy to debug |
| Boolean multiplication | =XLOOKUP(1, (names=A)*(quarters=B), sales) | Slightly faster on huge datasets; works without string concat |
| FILTER alternative | =FILTER(sales, (names=A)*(quarters=B)) | When the criteria match more than one row and you want all of them |
| Hidden helper column | Concat key in column E, then plain XLOOKUP on E | Spreadsheets read by less technical users — explicit and inspectable |
Why XLOOKUP needs help with multi-criteria
XLOOKUP's API takes one lookup value matched against one lookup array. When you need two criteria, you have to build the second one yourself.
XLOOKUP's signature is XLOOKUP(lookup_value, lookup_array, return_array, …) — single value, single array. If your table has two rows where the same person appears (Carol in Q1 and Carol in Q3), a plain XLOOKUP for "Carol" returns the FIRST match — usually the wrong one.
& (so "Carol" + "Q3" becomes a unique "CarolQ3" key), or use a boolean array that's 1 only where every criterion matches. Both turn a multi-criteria problem into a single-criterion problem that XLOOKUP's API can handle.The & concatenation method
Glue criteria together with the & operator. Most readable, fits in any version of XLOOKUP.
The pattern: concatenate both lookup values into one search key, concatenate both lookup arrays into one virtual array, let XLOOKUP do its single-key search against the joined array. The result is the row whose criteria matchboth values.
=XLOOKUP(F2 & G2, A2:A6 & C2:C6, D2:D6)Where
F2 = name, G2 = quarter, A2:A6 = names column, C2:C6 = quarters column, D2:D6 = sales column.Why this works: Excel evaluates the array expression A2:A6 & C2:C6 as a virtual array of joined strings — CarolQ1, CarolQ2, CarolQ3, BobQ1, BobQ3 — exactly five elements. Your search key "CarolQ3" matches the third element, and XLOOKUP returns the corresponding value from D2:D6.
Three or more criteria
Just keep concatenating. =XLOOKUP(A & B & C, names & quarters & regions, sales) works the same way. Watch the criteria order — both sides of the formula must use the same sequence, or the joined keys won't align.
A subtle gotcha: ambiguous concatenation
What if your concat could produce the same key from different source pairs? "Ab" & "Cd" and "A" & "bCd" both produce "AbCd". With real data this is rare, but if your criteria look like that, add a separator — A & "|" & B — to keep keys unambiguous.
The boolean multiplication method
Multiply boolean arrays. A 1 means “every criterion matches”; a 0 means at least one failed.
The pattern: each criterion produces an array of TRUE/FALSE values across the rows. Multiplying booleans coerces them to 1/0, and the product is 1 only on rows where every criterion is TRUE. Then XLOOKUP searches for the value 1 in that product array.
=XLOOKUP(1, (A2:A6 = F2) * (C2:C6 = G2), D2:D6)The first argument is the literal number 1 — the value we're searching for in the product. The product itself is built inline.
Trace through: A2:A6 = "Carol" produces {TRUE; TRUE; TRUE; FALSE; FALSE}. C2:C6 = "Q3" produces {FALSE; FALSE; TRUE; FALSE; TRUE}. The product is {0; 0; 1; 0; 0} — only row 4 has 1. XLOOKUP finds the 1 and returns row 4's sales.
When to prefer boolean over &
Two reasons. Performance on very large data: string concatenation across thousands of rows is slower than a boolean comparison. Type safety: if one of your criteria is a number (not text), boolean comparison handles it directly; concat would have to stringify the number first.
The cost is readability — the boolean version is harder to explain to a teammate looking at the formula bar.
FILTER for >1 result
XLOOKUP returns the first match. If “match both” produces multiple rows, switch to FILTER.
Imagine the same multi-criteria task but your data has the same pair (e.g. Carol in Q3) twice — once for Design region, once for Sales region — and you want both sales numbers. XLOOKUP can't do that. FILTER can.
=FILTER(D2:D6, (A2:A6 = F2) * (C2:C6 = G2))Returns an array of every matching value. In Excel 365 and modern Sheets it spills automatically into a vertical range.
Edge case: if no row matches both criteria, FILTER returns #CALC! (Excel) or #N/A (Sheets). Wrap in IFERROR(FILTER(…), "Not found") if you need a fallback.
Which method to use when
Three patterns, one decision per scenario. Most cases land on the & concat method.
| Your situation | Use this |
|---|---|
| Default — single result, two or three criteria, all text | & concat: XLOOKUP(A & B, names & quarters, sales) |
| Criteria are numbers (not text) | Boolean: XLOOKUP(1, (nums = A) * (quarters = B), sales) |
| Data is > 50,000 rows and performance is an issue | Boolean — skips the string concatenation step |
| Multiple rows match both criteria, you want them all | FILTER: FILTER(sales, (names = A) * (quarters = B)) |
| Sharing with non-technical users who'll inspect the formula | Helper column for the concat key + plain XLOOKUP |
| Concat keys could be ambiguous (overlapping strings) | Add a separator: A & "|" & B |
Excel & Sheets compatibility
All three patterns are platform-portable. Small differences in dynamic-array behaviour are worth knowing.
| Pattern | Excel 365 / 2021 | Excel 2019 ← | Google Sheets |
|---|---|---|---|
| & concatenation | ✅ Works as shown | ❌ No XLOOKUP at all | ✅ Identical |
| Boolean multiplication | ✅ Works (auto-array) | ❌ No XLOOKUP at all | ✅ Identical |
| FILTER for multi-row | ✅ Spills automatically | ❌ FILTER also missing | ✅ Spills |
| Helper column | ✅ | Use VLOOKUP on the helper column instead of XLOOKUP | ✅ |
For pre-365 Excel, the closest match is INDEX(MATCH(...)) with the same & concat or boolean multiplication trick: =INDEX(D2:D6, MATCH(F2 & G2, A2:A6 & C2:C6, 0)). Same intuition, different function shell.
Related
Main XLOOKUP reference and adjacent patterns on this site.
FAQ
7.01How do you use XLOOKUP with multiple criteria?▸
XLOOKUP doesn't have a built-in multi-criteria argument, but three patterns work cleanly. (1) The & concatenation method joins both lookup values and both lookup columns into one virtual key: =XLOOKUP(A2 & B2, names & quarters, sales). (2) The boolean multiplication method: =XLOOKUP(1, (names = A2) * (quarters = B2), sales) — the array product is 1 only where both criteria match. (3) The FILTER alternative for when you want all matching rows, not just the first: =FILTER(sales, (names = A2) * (quarters = B2)). The concat method is the most common; boolean works on dynamic arrays without changes; FILTER returns multiple rows when needed.
7.02What's the easiest way to do XLOOKUP with two conditions?▸
Concatenate both lookup values with & and both lookup columns with & — XLOOKUP will treat them as a single joined key. =XLOOKUP("Carol" & "Q3", A2:A6 & C2:C6, D2:D6) finds the sales for Carol in Q3, even though Carol appears in multiple rows. Easiest pattern in both Excel and Google Sheets; reads almost like its English-language description.
7.03Why does my XLOOKUP only return the first match?▸
XLOOKUP returns the first match in lookup_array by default. If your lookup_value appears multiple times (same name across several quarters), XLOOKUP picks the topmost. Build a composite key from your criteria — name & quarter against names & quarters — so only the right row produces a match. To scan from the bottom instead, pass search_mode = -1 as the 6th argument.
7.04Does XLOOKUP with multiple criteria work in Google Sheets?▸
Yes — both the & concatenation and the boolean multiplication patterns work identically in Google Sheets' XLOOKUP. Same formulas, same behaviour. Sheets also has a regex match_mode (3) that Excel doesn't, which can sometimes replace concatenation when one criterion is a pattern match. For multi-row returns, Sheets' FILTER works identically to Excel 365's.
7.05Is boolean multiplication faster than & concatenation in XLOOKUP?▸
Marginally, on very large datasets. The boolean method avoids string concatenation, which is slower than numeric comparison; on tens of thousands of rows you may see the difference. On typical spreadsheets (a few thousand rows) both are imperceptible. The & method is easier to read and debug, so prefer it unless you have a measured performance reason to switch. One edge case: if a criterion column contains numbers stored as text, the array comparison can return FALSE where you expect TRUE — wrap the column in VALUE() if mixed-type data bites you.
7.06How do I XLOOKUP with three or more criteria?▸
Both patterns scale. With concatenation, chain more & operators: =XLOOKUP(A2 & B2 & C2, names & quarters & regions, sales). With boolean multiplication, multiply more conditions: =XLOOKUP(1, (names = A2) * (quarters = B2) * (regions = C2), sales). Keep the criteria order consistent between the lookup_value and lookup_array sides — “name + quarter” must match “names & quarters” in the same order.