How to Use XLOOKUP in Google Sheets: Comprehensive Guide

XLOOKUP searches a range for a key and returns the matching item from another range. Unlike VLOOKUP, it does not care which column the key is in, it defaults to an exact match, it can look from the bottom up, and it lets you choose what to show when nothing is found. For new spreadsheets it is the lookup function to reach for first.

Syntax

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

search_key: The value to look for.
lookup_range: The single row or column to search in.
result_range: The row or column to return a value from. It must be the same size as lookup_range.
missing_value [Optional]: What to return when there is no match. If omitted, XLOOKUP returns #N/A.
match_mode [Optional]:
- 0 exact match (default)
- -1 exact match, or the next smaller value
- 1 exact match, or the next larger value
- 2 wildcard match (* and ?)
search_mode [Optional]:
- 1 search first to last (default)
- -1 search last to first
- 2 binary search, ascending data
- -2 binary search, descending data

Quick example

With product names in A2:A6 and prices in C2:C6:

=XLOOKUP("Stapler", A2:A6, C2:C6, "Not found")

Returns the price of the stapler, or Not found if the product is not in the list. Because the lookup range and the result range are separate, the price column can be anywhere: to the left of the names, on another sheet, or in a different file through IMPORTRANGE.

Looking to the left, with no column counting

With VLOOKUP you count columns and the key must be on the left. With XLOOKUP you point at the columns directly:

=XLOOKUP(E2, B2:B100, A2:A100)

This finds the value of E2 in column B and returns the matching item from column A. Inserting or deleting columns in the table does not break the formula, because nothing is hard-coded as a column number.

Match modes

Approximate match without sorting

VLOOKUP's approximate match needs sorted data. XLOOKUP with match_mode -1 returns the next smaller value and does not require the data to be sorted. For a tax-bracket or discount table where thresholds are in F2:F6 and rates in G2:G6:

=XLOOKUP(B2, F2:F6, G2:G6, , -1)

If the amount is 8,500 and the thresholds are 0, 5,000 and 10,000, it returns the rate for the 5,000 tier. Note the empty fourth argument, which skips missing_value.

Wildcards

Use match_mode 2 to match partial text:

=XLOOKUP("*" & E2 & "*", A2:A100, B2:B100, "No match", 2)

This returns the first row whose name contains the text in E2.

Search from the bottom: the latest entry

When a key appears several times, XLOOKUP returns the first match by default. Set search_mode to -1 to start from the bottom and get the most recent entry in a log:

=XLOOKUP(E2, A2:A1000, C2:C1000, "None", 0, -1)

This is a common way to get a customer's latest order or the last recorded price.

Return several columns at once

If the result range is several columns wide, XLOOKUP returns the whole matching row segment and spills it across cells:

=XLOOKUP(E2, A2:A100, B2:D100)

This returns the values of B, C and D for the matching row in a single formula.

Two criteria

To match on two columns, such as name and month, combine them inside the lookup:

=XLOOKUP(E2 & F2, ARRAYFORMULA(A2:A100 & B2:B100), C2:C100, "Not found")

Common errors

#N/A. No match, and no missing_value was provided. Check for extra spaces (use TRIM) and for text-versus-number mismatches, such as "1001" against 1001. Or pass a missing_value.
#VALUE!. lookup_range and result_range are not the same size, or you passed a two-dimensional lookup range. Each must be a single row or a single column, and they must have the same length.
Wrong row returned. Duplicate keys: XLOOKUP returns the first match unless you set search_mode to -1.

XLOOKUP vs. VLOOKUP vs. INDEX/MATCH

Direction: XLOOKUP and INDEX/MATCH look left or right. VLOOKUP only looks right.
Default match: XLOOKUP and INDEX/MATCH are exact. VLOOKUP is approximate unless you pass FALSE.
Not found: XLOOKUP has a built-in missing_value. The others need IFERROR.
Column changes: XLOOKUP references columns directly, so inserting columns is safe.
Compatibility: If a file will also be opened in older tools, VLOOKUP and INDEX/MATCH are the most widely supported.

For a deeper comparison, see XLOOKUP vs INDEX-MATCH in Google Sheets 2026.

Video Example

Frequently asked questions

Is XLOOKUP case-sensitive? No. For a case-sensitive lookup, combine INDEX, MATCH and EXACT.

Can I look up an entire row or table? Yes, make result_range multiple columns wide. For all matches, not just the first, use FILTER.

Does XLOOKUP work with ARRAYFORMULA? Yes. Wrap it as ARRAYFORMULA(XLOOKUP(E2:E, ...)) to fill a column, though a spilling XLOOKUP over a range often does not need it.

Related Functions

VLOOKUP: The classic vertical lookup that XLOOKUP replaces.
HLOOKUP: Horizontal lookup across the top row.
INDEX: Returns the content of a cell by row and column offset.
MATCH: Returns the relative position of an item in a range.
FILTER: Returns all rows matching a condition, not just the first.

XLOOKUP