XLOOKUP with Multiple Criteria in Google Sheets
XLOOKUP looks up one value, but often you need two conditions at once: the units for South and Paper. The usual trick is to join the conditions with &. In Google Sheets that trick fails unless you add one more function. We tested it, and an alternative with FILTER.
"Array arguments to XLOOKUP are of different size"
Our table has Region in A, Product in B and Units in C. To find the units for the region in F2 and the product in G2, the formula most guides show is:
=XLOOKUP(F2&G2, A2:A6&B2:B6, C2:C6)In Google Sheets it returned #N/A with "Array arguments to XLOOKUP are of different size." Typed on its own, A2:A6&B2:B6 doesn't build a list of five joined values; Sheets only joins them as a whole column when the formula runs in array mode.
Fix: wrap it in ARRAYFORMULA
=ARRAYFORMULA(XLOOKUP(F2&G2, A2:A6&B2:B6, C2:C6))
ARRAYFORMULA makes Sheets join every row of A and B first (NorthPens, NorthPaper, SouthPens…), and then XLOOKUP searches that list for SouthPaper. In our test it returned 12, the right answer.
You can also press Ctrl+Shift+Enter while editing the formula, which adds ARRAYFORMULA for you. More in how to use ARRAYFORMULA.
Watch out for joined values that collide. "AB" + "C" and "A" + "BC" both become "ABC". If that can happen in your data, join with a separator: F2&"|"&G2 and A2:A6&"|"&B2:B6.
Alternative: FILTER
=FILTER(C2:C6, A2:A6=F2, B2:B6=G2)FILTER takes each condition separately, so there's nothing to join and no ARRAYFORMULA needed. It returned 12 too. Two differences from XLOOKUP:
- If several rows match, FILTER returns all of them (they spill down), while XLOOKUP returns the first.
- If nothing matches, FILTER gives an error instead of a default value; wrap it in IFERROR to show a message.
See how to filter data for more FILTER examples.
When there's no match
For North + Pencils, which isn't in the table, we added XLOOKUP's fourth argument, the value to show when nothing is found:
=ARRAYFORMULA(XLOOKUP(F3&G3, A2:A6&B2:B6, C2:C6, "Not found"))It showed Not found instead of an error.
Frequently asked questions
Does the same trick work with VLOOKUP?
VLOOKUP can't join columns inside the formula; you'd need a helper column with the joined values. XLOOKUP or FILTER is simpler. See how to use XLOOKUP.
Can I use more than two conditions?
Yes: join more columns (A2:A6&B2:B6&D2:D6) with ARRAYFORMULA, or add more conditions to FILTER, one per argument.