How to Use XLOOKUP in Google Sheets (with Examples)
XLOOKUP is the modern replacement for VLOOKUP. You tell it where to search and where to return from as two separate ranges, so the result column can be to the left or right, and there's a built-in option for values that aren't found.
The syntax
=XLOOKUP(search_key, lookup_range, result_range, [missing_value])
- search_key: what you're looking for, e.g. a product ID.
- lookup_range: the single column to search in.
- result_range: the column to return a value from (same size as lookup_range).
- missing_value (optional): what to show if nothing is found.
Example
A Products tab lists IDs in column A, names in B and prices in C. To get the name for the ID in A2:
=XLOOKUP(A2, Products!A2:A5, Products!B2:B5, "Not found")
The price column uses the same formula with Products!C2:C5 as the result range. Prefer VLOOKUP? See VLOOKUP from another sheet.
XLOOKUP vs VLOOKUP
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Result column | Only to the right, by number | Anywhere, as a range |
| Default match | Needs FALSE for exact | Exact by default |
| Not found | #N/A (wrap in IFNA) | Built-in missing_value |
| Breaks if columns are inserted | Yes (column number shifts) | No |
For older files or colleagues used to it, see VLOOKUP. INDEX and MATCH is the classic flexible alternative.
Useful variations
| You want… | Formula |
|---|---|
| Return several columns at once | =XLOOKUP(A2, Products!A2:A5, Products!B2:C5) |
| Search from the bottom (last match) | =XLOOKUP(A2, A2:A100, C2:C100, , 0, -1) |
| Next smaller value (e.g. tax bands) | =XLOOKUP(B2, E2:E6, F2:F6, , -1) |
We tested all three: the two-column version returned "Desk lamp" and 24 side by side; searching from the bottom found the last of two matches; and looking up 25 in the bands 0, 10, 20, 30 with -1 returned the band for 20. Looking to the left also works: =XLOOKUP("Desk lamp", Products!B2:B5, Products!A2:A5) returned P-103.
Looking up with two conditions? Joining them with & needs ARRAYFORMULA in Google Sheets, or you get "Array arguments to XLOOKUP are of different size". See XLOOKUP with multiple criteria.
INDEX MATCH: the older alternative
Before XLOOKUP, the usual way to look up a value in any direction was INDEX with MATCH. You'll still see it in older files:
=INDEX(C2:C5, MATCH(E2, A2:A5, 0))
In our test, =INDEX(Products!C2:C5, MATCH(B2, Products!A2:A5, 0)) returned 24, the same as XLOOKUP. Read it inside-out: MATCH finds the position of E2 in column A (the 0 means exact match), and INDEX returns the value in that same position from column C. Like XLOOKUP, it can look to the left and doesn't break when you insert a column, but it's harder to read, so in Google Sheets XLOOKUP is usually the better choice.
XLOOKUP with multiple criteria
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
Why do I get "Array arguments to XLOOKUP are of different size"?
The lookup range and result range have different sizes. In our test, Products!A2:A5 with Products!B2:B4 showed #N/A with that message. Make them cover the same rows.
Is XLOOKUP available in every Google Sheets account?
Yes, it's a standard function in Google Sheets. It also exists in newer versions of Excel.