How to Use XLOOKUP in Google Sheets (with Examples)

By Gerard Fernandez · Updated · 3 min read

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")
Orders tab in Google Sheets: XLOOKUP in column D returns Desk lamp, Notebook and Stapler from the Products tab, and Not found for P-109, while VLOOKUP in column C shows #N/A for the same ID
Real test: on an Orders tab, XLOOKUP (column D) brings the product name from the Products tab. For the unknown ID P-109 it shows "Not found"; the VLOOKUP next to it shows #N/A.

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

VLOOKUPXLOOKUP
Result columnOnly to the right, by numberAnywhere, as a range
Default matchNeeds FALSE for exactExact by default
Not found#N/A (wrap in IFNA)Built-in missing_value
Breaks if columns are insertedYes (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"

Google Sheets =XLOOKUP(F2&G2, A2:A6&B2:B6, C2:C6) showing #N/A with the tooltip Array arguments to XLOOKUP are of different size.
South + Paper exists (12 units), but the formula returns #N/A.

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))
Google Sheets with =ARRAYFORMULA(XLOOKUP(F2&G2, A2:A6&B2:B6, C2:C6)) returning 12 in the Fixed column, and FILTER also returning 12
With ARRAYFORMULA the same lookup returns 12. FILTER (column J) agrees.

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.