XLOOKUP function in Google Sheets

XLOOKUP finds a value in one range and returns the matching item from another, in any direction.

XLOOKUP is the newer lookup function, added to Google Sheets in 2022. It does what VLOOKUP does with fewer traps: exact match by default, any direction, and a built-in answer for a missing value.

Syntax

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

Argument	What it is
`search_key`	The value to look for.
`lookup_range`	The single row or column to search.
`result_range`	The row or column to return from, the same size as the lookup range.
`missing_value`	What to show if nothing is found. Without it the result is #N/A.
`match_mode`	0 exact (the default), 1 exact or next larger, -1 exact or next smaller, 2 wildcard.
`search_mode`	1 from the first item (the default), -1 from the last.

Examples

Sample sheet: A small price list with item codes. The table is sorted by code.

	A	B	C	D	E
1	Code	Item	Category	Price	Stock
2	A101	Basmati Rice 5 kg	Grocery	650	40
3	A102	Toor Dal 1 kg	Grocery	160	75
4	B201	Notebook	Stationery	60	300
5	B202	Ball Pen	Stationery	10	0
6	C301	LED Bulb	Electronics	110	200
7	C302	Power Bank	Electronics	1299	18
8	D401	Steel Bottle	Home	450	55

=XLOOKUP("C301",A2:A8,B2:B8)
Result: LED Bulb
Search the codes, return the item. Exact match is the default.
=XLOOKUP("Notebook",B2:B8,A2:A8)
Result: B201
A lookup to the left: search the names, return the code.
=XLOOKUP("Z999",A2:A8,B2:B8,"Not found")
Result: Not found
The fourth argument is the message for a missing key.
=XLOOKUP("D401",A2:A8,D2:E8)
Result:
450 55
A result range two columns wide returns two cells.
=XLOOKUP(500,D2:D8,B2:B8,,-1)
Result: Steel Bottle
Match mode -1: no price is exactly 500, so the next smaller one, 450, is used.

Sample sheet: Twelve sales made by four salespeople in three regions.

	A	B	C	D	E	F
1	Date	Salesperson	Region	Product	Units	Amount
2	05-Jan-2026	Asha	North	Notebook	20	1200
3	12-Jan-2026	Ravi	South	Pen	50	500
4	20-Jan-2026	Meena	North	Bag	4	3000
5	02-Feb-2026	Asha	East	Pen	30	300
6	14-Feb-2026	Kabir	South	Notebook	15	900
7	28-Feb-2026	Ravi	North	Bag	2	1500
8	03-Mar-2026	Meena	East	Notebook	40	2400
9	15-Mar-2026	Asha	North	Bag	3	2250
10	22-Mar-2026	Kabir	South	Pen	60	600
11	01-Apr-2026	Ravi	East	Notebook	25	1500
12	18-Apr-2026	Meena	North	Pen	80	800
13	06-May-2026	Asha	South	Bag	5	3750

=XLOOKUP("Asha",B2:B13,F2:F13,,0,-1)
Result: 3750
Search mode -1 starts from the bottom: Asha's most recent sale.

How it works

XLOOKUP takes two separate ranges: one to search, one to answer from. It finds the position of the key in the first and returns the item at the same position in the second. There is no column number, and it does not matter which range is on the left.

Compared with VLOOKUP

	VLOOKUP	XLOOKUP
Default match	sorted (approximate)	exact
Direction	key in the first column, answer to its right	any
Insert a column	may break the index	no effect
Missing key	needs IFNA	fourth argument
Last match	no	search from the end

Match and search modes

Match mode -1 returns the next smaller item when there is no exact match, and 1 the next larger. Unlike VLOOKUP's sorted mode, the data need not be sorted. Mode 2 allows the wildcards * and ?.

Search mode -1 searches from the last item upwards, which returns the latest entry in a list kept in date order, as the last example shows.

Returning a whole row

If the result range is several columns wide, XLOOKUP returns the whole row of results across the cells to the right. One formula can bring back the name, the category and the price for a code.

Across a whole column

Unlike VLOOKUP, XLOOKUP is awkward to expand with ARRAYFORMULA when it must return one value per row for a column of keys. It works in simple cases, =ARRAYFORMULA(XLOOKUP(A2:A10, Codes, Names)), but if you need a self-filling column, VLOOKUP inside ARRAYFORMULA is the well-trodden path.

When to use which

New sheets, used only in Google Sheets or in modern Excel: XLOOKUP.
Sheets that will be downloaded as Excel files for people on Excel 2019 or earlier: VLOOKUP or INDEX and MATCH, since XLOOKUP shows #NAME? there.
Every matching row, not just the first: FILTER.

Common mistakes

Lookup and result ranges of different sizes, which gives an error.
Skipping an optional argument without its comma. To set match mode but no missing value, write ,,-1.
Numbers on one side and text digits on the other.
Expecting more than the first match.

In Excel

XLOOKUP exists in Excel 2021 and Microsoft 365 with the same six arguments, under slightly different names. Formulas carry over unchanged.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.