XLOOKUP searches one column. To find a row that matches two or three conditions at once, such as a region and a product, you turn the conditions into a single column of 1s and 0s and look for the 1. There is one catch that most examples leave out: in Google Sheets this formula only works inside ARRAYFORMULA. Without it, you get #VALUE! or a “different size” error, depending on which row the formula is in.
The examples use this table in A1:E9:
| A: Region | B: Product | C: Month | D: Units | E: Price | |
|---|---|---|---|---|---|
| 2 | North | Apple | Jan | 10 | 1.2 |
| 3 | North | Banana | Jan | 5 | 0.5 |
| 4 | South | Apple | Jan | 8 | 1.2 |
| 5 | North | Apple | Feb | 12 | 1.3 |
| 6 | South | Banana | Feb | 7 | 0.6 |
| 7 | East | Cherry | Feb | 3 | 3 |
| 8 | North | Apple | Mar | 15 | 1.25 |
| 9 | South | Apple | Mar | 9 | 1.25 |
Tested in Google Sheets. We ran every formula on this page on November 12, 2026, including the broken versions, and copied the error messages word for word. How we test
The formula
To get the units for North and Apple:
=ARRAYFORMULA(XLOOKUP(1, (A2:A9="North")*(B2:B9="Apple"), D2:D9))
This returned 10, from row 2, the first row where both conditions are true. How it works:
(A2:A9="North")gives TRUE or FALSE for every row, and so does(B2:B9="Apple").- Multiplying them turns TRUE into 1 and FALSE into 0. The product is 1 only where both are true: 1, 0, 0, 1, 0, 0, 1, 0.
- XLOOKUP looks for the first 1 in that list and returns the value from the same position in D2:D9.
For a third condition, multiply once more. North, Apple and Feb returned 12:
=ARRAYFORMULA(XLOOKUP(1, (A2:A9="North")*(B2:B9="Apple")*(C2:C9="Feb"), D2:D9))
With the values typed in G1, G2 and G3 instead of the formula, the result was the same: =ARRAYFORMULA(XLOOKUP(1, (A2:A9=G1)*(B2:B9=G2)*(C2:C9=G3), D2:D9)) returned 12.
If you don’t want to write it by hand, our lookup formula generator builds it for up to three conditions, with ARRAYFORMULA already in place.
Why it fails without ARRAYFORMULA
Many tutorials show the formula without ARRAYFORMULA, because that is how it works in Excel. In Google Sheets, =XLOOKUP(1, (A2:A9="North")*(B2:B9="Apple"), D2:D9) did not work in any of the cells where we tried it. What you see depends on the row:
| Where the formula is | What we got |
|---|---|
| On a row outside the data (row 1, or row 10 and below) | #VALUE!, The default output of this reference is a single cell in the same row but a matching value could not be found. To get the values for the entire range use the ARRAYFORMULA function. |
| On one of the data rows (2 to 9) | #N/A, Array arguments to XLOOKUP are of different size. |
Without ARRAYFORMULA, A2:A9="North" compares only the cell of A2:A9 on the formula’s own row, not the whole column. Outside the data there is no such cell, hence the first message; inside the data you get a single TRUE or FALSE, which is smaller than D2:D9, hence the second. The first message even tells you the fix.
Wrapping only the conditions also worked: =XLOOKUP(1, ARRAYFORMULA((A2:A9="North")*(B2:B9="Apple")), D2:D9) returned 10. Wrapping the whole formula is simpler to remember.
Found in testing: INDEX MATCH with the same multiplied conditions, =INDEX(D2:D9, MATCH(1, (A2:A9="North")*(B2:B9="Apple"), 0)), returned 10 without ARRAYFORMULA, both on a data row and outside the data. Only XLOOKUP needed it. We compare the three lookup functions in VLOOKUP vs XLOOKUP vs INDEX MATCH.
The last match instead of the first
North and Apple appear on three rows. To get the most recent one, at the bottom, add the match mode 0 and the search mode -1, which searches from the last row up:
=ARRAYFORMULA(XLOOKUP(1, (A2:A9="North")*(B2:B9="Apple"), D2:D9, , 0, -1))
This returned 15, from row 8. The empty argument between the two commas is the “not found” value, left out.
Return several columns
Give XLOOKUP a wider result range and it returns the whole matching row of that range. =ARRAYFORMULA(XLOOKUP(1, (A2:A9="North")*(B2:B9="Apple"), C2:E9)) returned Jan, 10, 1.2 across three cells. The cells to the right must be empty. More on this, including columns in a different order, in XLOOKUP returning multiple columns.
When nothing matches
There is no West region, so =ARRAYFORMULA(XLOOKUP(1, (A2:A9="West")*(B2:B9="Apple"), D2:D9)) returned #N/A with Did not find value ‘1’ in XLOOKUP evaluation. The ‘1’ in the message is the 1 you searched for, not a value from your data, which makes this error confusing the first time.
To show your own text, use the fourth argument: =ARRAYFORMULA(XLOOKUP(1, (A2:A9="West")*(B2:B9="Apple"), D2:D9, "Not found")) returned “Not found”.
Conditions other than “equals”
Any comparison works, as long as it gives TRUE or FALSE for each row. The first North row with more than 10 units:
=ARRAYFORMULA(XLOOKUP(1, (A2:A9="North")*(D2:D9>10), C2:C9))
This returned Feb (12 units in row 5).
OR instead of AND: add, but use SIGN
To find the first row where either condition is true, you add the conditions instead of multiplying them. But a row where both are true adds up to 2, not 1, and XLOOKUP looks for exactly 1.
In our table, =ARRAYFORMULA(XLOOKUP(1, (A2:A9="North")+(B2:B9="Apple"), B2:B9)) returned Banana, from row 3, with no error. Row 2 (North and Apple) was skipped because its sum was 2. SIGN turns any positive number into 1:
=ARRAYFORMULA(XLOOKUP(1, SIGN((A2:A9="North")+(B2:B9="Apple")), B2:B9))
This returned Apple, from row 2, the right answer.
The concatenation method
The other common approach joins the columns into one text and looks for the joined value:
=ARRAYFORMULA(XLOOKUP("North|Apple|Feb", A2:A9&"|"&B2:B9&"|"&C2:C9, D2:D9))
It returned 12, and it also needs ARRAYFORMULA: without it, we got the same “different size” error. Keep the separator. Joining without one can match the wrong row: in a two-row test, “ab” + “c” and “a” + “bc” both became “abc”, and XLOOKUP("ab"&"c", …) returned the first of them, the wrong one. With "|" between the parts, it returned the right row. The multiplication method has no such risk and also handles comparisons such as “more than 10”, so we use it by default.
Mistakes that give no error
- Ranges of different lengths. With one range ending a row early,
(B2:B8="Apple"), the formula inside ARRAYFORMULA did not complain. It returned 10 for North and Apple, but #N/A for South, Apple and Mar, which is on row 9, the row the short range left out. Make every range start and end on the same rows. - Searching for TRUE instead of 1.
XLOOKUP(TRUE, (A2:A9="North")*(B2:B9="Apple"), D2:D9)returned #N/A, Did not find value ‘TRUE’. After multiplying, the list holds numbers, not TRUE and FALSE.
Upper and lower case
The comparisons ignore case: (A2:A9="north")*(B2:B9="apple") still found North and Apple and returned 10. To tell them apart, use EXACT: EXACT(A2:A9, "north")*(B2:B9="Apple") returned #N/A, because no region is written in lowercase.
Every match, or a total
XLOOKUP returns one row. For all of them, FILTER is simpler and needs no ARRAYFORMULA, because each condition is its own argument:
=FILTER(D2:D9, A2:A9="North", B2:B9="Apple")
This returned 10, 12 and 15 in three cells down. If you want the total rather than the list, =SUMIFS(D2:D9, A2:A9, "North", B2:B9, "Apple") returned 37.
Related guides
- XLOOKUP returning multiple columns
- VLOOKUP vs XLOOKUP vs INDEX MATCH, tested
- INDEX MATCH not working: 9 causes
- How to replace #N/A with a blank
- Count rows that meet several conditions (COUNTIFS)
- Return every matching row with FILTER
- ARRAYFORMULA with IF, and why AND breaks it
- Lookup formula generator