In Google Sheets, a two-condition XLOOKUP needs ARRAYFORMULA: =ARRAYFORMULA(XLOOKUP(1, (A2:A100="Dana")*(B2:B100="Pocket Watch"), C2:C100)). The comparisons multiply into an array of 0s and 1s and XLOOKUP searches it for 1. Without the ARRAYFORMULA wrapper the formula fails with #VALUE!, which is the step most Excel-focused guides skip.
advanced 3 min read
XLOOKUP with Multiple Criteria in Google Sheets
Look up with two or more conditions using XLOOKUP in Google Sheets. The ARRAYFORMULA wrapper Excel guides leave out, plus the concatenation alternative.
SB
Sheets Bootcamp
July 28, 2026
Table of Contents
Quick Answer
XLOOKUP searches one range. A lookup with two or more conditions, like “Dana’s order for the Pocket Watch,” seems to need something more, but the trick is to build a temporary range that encodes all your conditions at once, then search that.
One warning before the recipe: most tutorials for this technique are written for Excel, and the Excel version does not work in Google Sheets. We tested every formula on this page in a live sheet; the difference is one wrapper function.
In This Guide
- The Working Formula
- How It Works
- Why Excel Guides Fail in Google Sheets
- More Conditions, OR Logic, and Last Match
- The Concatenation Alternative
- Related Google Sheets Tutorials
The Working Formula
The order log, where neither column is unique on its own:
| Customer | Product | Amount |
|---|---|---|
| Dana | Magnifying Glass | $24.99 |
| Marcus | Pocket Watch | $35.00 |
| Dana | Field Binoculars | $65.00 |
| Priya | Cipher Decoder | $28.50 |
| Dana | Magnifying Glass | $27.50 |
Dana’s Field Binoculars order:
Formula
=ARRAYFORMULA(XLOOKUP(1, (A2:A6="Dana")*(B2:B6="Field Binoculars"), C2:C6)) The formula returns $65.00, the one row where both conditions hold.
How It Works
Take the formula apart from the inside out:
A2:A6="Dana"compares every customer cell and producesTRUE, FALSE, TRUE, FALSE, TRUEB2:B6="Field Binoculars"producesFALSE, FALSE, TRUE, FALSE, FALSE- Multiplying the two converts TRUE/FALSE to 1/0 and gives
0, 0, 1, 0, 0. A row can only be 1 if every condition on it is TRUE - XLOOKUP searches that array for
1and returns the matching row from the result range
The search key is the number 1, the lookup range is an array you computed on the fly, and everything else behaves like a normal XLOOKUP, including missing_value and search_mode.
Why Excel Guides Fail in Google Sheets
The formula you will find in most tutorials looks like this:
Formula
=XLOOKUP(1, (A2:A6="Dana")*(B2:B6="Field Binoculars"), C2:C6) In Excel, that works: Excel evaluates array expressions natively. In Google Sheets it returns #VALUE!, because Sheets does not evaluate the elementwise multiplication as an array unless something asks it to. That something is ARRAYFORMULA, wrapped around the entire XLOOKUP.
Warning
If your multiple-criteria XLOOKUP shows #VALUE!, you are almost certainly looking at a pasted Excel formula. Wrap it: =ARRAYFORMULA(XLOOKUP(...)). We verified both versions against a live sheet: the bare formula fails, the wrapped one returns the right answer.
More Conditions, OR Logic, and Last Match
Three or more conditions multiply on the same way:
Formula
=ARRAYFORMULA(XLOOKUP(1, (A2:A100="Dana")*(B2:B100="Magnifying Glass")*(C2:C100>25), D2:D100)) Conditions are not limited to equality: >, <, >=, <=, and <> all produce TRUE/FALSE arrays the same way.
OR logic uses addition instead of multiplication, then checks for “at least 1”:
Formula
=ARRAYFORMULA(XLOOKUP(1, --((A2:A100="Dana")+(A2:A100="Priya")>0), C2:C100)) Last match instead of first: add search_mode -1. With duplicate Dana + Magnifying Glass rows in the log above, this returns the later one, $27.50:
Formula
=ARRAYFORMULA(XLOOKUP(1, (A2:A6="Dana")*(B2:B6="Magnifying Glass"), C2:C6, "none", 0, -1)) That combination, several conditions plus bottom-up search, is the one-line answer to “the most recent order of this product by this customer.”
The Concatenation Alternative
The other approach glues the criteria columns together and searches the combined text:
Formula
=ARRAYFORMULA(XLOOKUP("Dana|Field Binoculars", A2:A6&"|"&B2:B6, C2:C6)) The same Google Sheets rule applies: the concatenation of two ranges is an array operation, so the bare version fails (with #N/A rather than #VALUE!, making it extra confusing) and the ARRAYFORMULA version works.
Two cautions with this style. First, always include a separator character: without the "|", the pair “Sam” + “sonite” would collide with “Samson” + “ite”. Second, pick a separator that can never appear in the data itself. The boolean multiplication method has neither problem, which is why it is the default recommendation.
Note
Both methods scan every row, so on very large sheets they cost more than a plain XLOOKUP. If a multi-criteria lookup becomes a bottleneck, a helper column holding the concatenated key once, with a plain XLOOKUP against it, is the fast path.
Related Google Sheets Tutorials
- XLOOKUP: The Complete Guide: The function this builds on
- XLOOKUP Search Modes: The last-match trick in depth
- INDEX MATCH with Multiple Criteria: The older pattern for the same job
- ARRAYFORMULA Guide: Why Google Sheets needs the wrapper
- XLOOKUP Errors: Decoding #VALUE! and #N/A
Frequently Asked Questions
Can XLOOKUP handle multiple criteria in Google Sheets?
Yes, by multiplying comparisons into a single array and searching it for 1: =ARRAYFORMULA(XLOOKUP(1, (A2:A100="Dana")*(B2:B100="Pocket Watch"), C2:C100)). In Google Sheets the ARRAYFORMULA wrapper is required.
Why does my XLOOKUP multiple criteria formula return #VALUE! in Google Sheets?
You are missing the ARRAYFORMULA wrapper. Excel evaluates (A:A=x)*(B:B=y) as an array automatically, but Google Sheets does not, so the Excel-style formula fails with #VALUE!. Wrap the whole XLOOKUP in ARRAYFORMULA and it works.
How does the (range=value)*(range=value) trick work?
Each comparison produces an array of TRUE and FALSE values. Multiplying converts them to 1s and 0s, and a row multiplies to 1 only when every condition is TRUE. XLOOKUP then searches that array of 0s and 1s for the first 1.
Can I combine multiple criteria with the last-match search?
Yes. Add search_mode -1 as the sixth argument: =ARRAYFORMULA(XLOOKUP(1, (A2:A100="Dana")*(B2:B100="Pocket Watch"), C2:C100, "none", 0, -1)) returns the most recent row matching both conditions.
Is the concatenation method or the boolean method better?
The boolean multiplication method is safer. Concatenating values like A2:A100&"|"&B2:B100 can produce false matches if your data ever contains the separator character, and it does more text work per row. Use concatenation only when you find it easier to read.
You've googled this formula before. Probably more than once.
Basic Training is a free course inside a real Google Sheet that grades every formula the moment you press Enter. 7 lessons, about 40 minutes, and this loop ends.
Try the Sheet That Grades YouFree. No spam, ever.