How to Use XLOOKUP in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Beginner to intermediate · 7 min read · includes 2 free exercises

In short: XLOOKUP finds a value in one column or row and returns the matching value from another. It matches exactly by default, it can return values from the left of the lookup column, and it lets you choose what to show when nothing is found.

What XLOOKUP does

Like VLOOKUP, XLOOKUP takes a value you have, finds it in a list, and returns related information from the same row. The difference is that you point to the column you search and the column you want back as two separate ranges, so you never count columns.

XLOOKUP syntax

=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

A step-by-step example

Here is the same product table used in the VLOOKUP guide, in cells A1:C5.

ABC
1IDProductPrice
2P-101Notebook4.50
3P-102Pen1.20
4P-103Backpack32.00
5P-104Stapler8.75

To find the price of P-102:

=XLOOKUP("P-102", A2:A5, C2:C5)

This returns 1.20. Here is why:

  1. Sheets searches A2:A5 for "P-102".
  2. It finds it in the second position of that range.
  3. It returns the second item of C2:C5, which is the price.

To get the product name instead, change the result range to B2:B5. You do not need an index number.

Look up to the left

VLOOKUP can only return columns to the right of the search column. XLOOKUP has no such limit. To find the ID of the product named Backpack:

=XLOOKUP("Backpack", B2:B5, A2:A5)

This returns P-103, even though the ID column is to the left of the product column.

Handle missing values

If the search key is not in the list, XLOOKUP returns #N/A. The fourth argument lets you show something friendlier without wrapping the formula in IFERROR:

=XLOOKUP("P-999", A2:A5, C2:C5, "Not found")

This returns Not found.

XLOOKUP vs. VLOOKUP

If you already use VLOOKUP and it works, there is no need to rewrite old sheets. For new formulas, XLOOKUP is usually the simpler choice. See the VLOOKUP guide for the older approach.

Common XLOOKUP errors

#N/A: "Did not find value"

Ranges of different sizes

The lookup range and the result range must have the same number of rows (or columns). A2:A5 with C2:C4 returns an error.

Try it yourself

Use the product table above (A1:C5). Type your formula into the box, starting with =, then press Check.

Exercise 1: Find a price

Write a formula that returns the price of product P-104, searching the IDs in column A.

Hint: the lookup range is A2:A5 and the result range is C2:C5.

Exercise 2: Look to the left

Write a formula that returns the ID of the product named Backpack, searching the names in column B.

Hint: search B2:B5 and return A2:A5. The result range can be to the left of the lookup range.

What to learn next

Syntax and behavior in this guide were checked against Google's XLOOKUP help page. See all guides.