XLOOKUP function in Excel

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

XLOOKUP is the modern replacement for VLOOKUP and HLOOKUP. It does the same job with fewer traps. It is available in Excel 2021 and Microsoft 365. Older versions, such as Excel 2016 and 2019, do not have it and show #NAME?.

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Argument	What it is
`lookup_value`	The value to look for.
`lookup_array`	The single column or row to search.
`return_array`	The column or row to return from. It must be the same size as the lookup array.
`if_not_found`	What to show when nothing matches. Without it you get #N/A.
`match_mode`	0 exact match (the default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match.
`search_mode`	1 search from the first item (the default), -1 search from the last.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

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
Finds C301 in column A and returns the item from column B. Exact match is the default.
=XLOOKUP("Notebook",B2:B8,A2:A8)
Result: B201
A lookup to the left: find the item name in column B, return its code from column A.
=XLOOKUP("Z999",A2:A8,B2:B8,"Not found")
Result: Not found
The fourth argument is the message for a missing value. No IFERROR needed.
=XLOOKUP("D401",A2:A8,D2:E8)
Result:
450 55
The return range has two columns, so two answers come back side by side: price and stock.
=XLOOKUP(500,D2:D8,B2:B8,,-1)
Result: Steel Bottle
Match mode -1: no item costs exactly 500, so the next smaller price, 450, is used.
=XLOOKUP("*Bulb*",B2:B8,D2:D8,,2)
Result: 110
Match mode 2 allows wildcards: the price of the first item whose name contains Bulb.

How it works

You give XLOOKUP two separate ranges: where to look, and where to take the answer from. It finds the position of the lookup value in the first range and returns the item at the same position in the second.

Because the two ranges are separate, there is no column number to count, and it does not matter which one is on the left.

Why it is better than VLOOKUP

Exact match is the default. There is no fourth argument to forget.
It looks in any direction: left, right, up or down.
Inserting columns does not break it, because the ranges move with the data.
A built-in message for missing values replaces the IFERROR wrapper.
It can return a whole row or several columns at once, as the fourth example shows. The answers spill into the cells to the right.
It can search from the bottom. Search mode -1 returns the last match, which is how you find the most recent entry in a log.

Match modes

match_mode	Meaning	Typical use
0	exact match	codes, names, IDs
-1	exact, or the next smaller item	grade bands, tax slabs
1	exact, or the next larger item	the next size up, the next delivery slot
2	wildcards `*` and `?`	part of a name

For the approximate modes the lookup range does not have to be sorted, another improvement over VLOOKUP.

Two-way lookup

Put one XLOOKUP inside another to find a value at the meeting point of a row label and a column label. On the months sheet, =XLOOKUP("Target",A1:A3,XLOOKUP("Mar",B1:G1,B1:G3)) would return the target for March. The inner formula returns the whole March column and the outer one picks the Target row from it.

Common mistakes

Ranges of different sizes. A2:A8 with B2:B9 gives #VALUE!. Both must have the same number of cells.
Sending the file to someone with an older Excel. Their copy shows #NAME?. If the workbook must work everywhere, use INDEX and MATCH.
Skipping arguments without the commas. To set match_mode but leave if_not_found empty, keep its place: =XLOOKUP(500,D2:D8,B2:B8,,-1).

In Google Sheets

Google Sheets also has XLOOKUP, with the same arguments.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

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