Is XLOOKUP better than VLOOKUP?

For most jobs, yes: it is exact by default, looks in any direction, does not break when columns are inserted and has a built-in not-found message. VLOOKUP still matters for compatibility with older Excel.

Can XLOOKUP return several columns?

Yes. Give a multi-column return range such as B2:D10 and the result spills across the columns.

Does XLOOKUP work in Excel 2019?

No. It is available in Excel 2021, Excel 2024 and Microsoft 365.

How do I do an approximate match with XLOOKUP?

Set the fifth argument match_mode to -1 (exact or next smaller) or 1 (exact or next larger). No sorting is required.

← All functions

Lookup & reference

XLOOKUP

Modern lookup in any direction.

Syntax

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

Example: =XLOOKUP(A2,B:B,C:C,"none")

Available in: Excel 2021+, Microsoft 365

XLOOKUP searches one range for a value and returns the matching item from another range, in any direction. It defaults to an exact match, can look left, and lets you set a custom message when nothing is found, so it replaces most VLOOKUP and INDEX/MATCH formulas in Microsoft 365 and Excel 2021 or later.

When to use it

You want to look up a value and return a column to the left of the key, which VLOOKUP cannot do.
You need a clean "not found" message without wrapping the formula in IFERROR.
You want the last match instead of the first (search from the bottom) or a nearest-value match.
You are building new workbooks for people who all have Microsoft 365 or Excel 2021 or later.

Arguments explained

lookup_value. The value you are searching for. It can be a single cell or, in Microsoft 365, a whole column of values that return several results at once.
lookup_array. The one-column or one-row range that is searched. Unlike VLOOKUP it can sit anywhere, including to the right of the results.
return_array. The range the answer is taken from. It must have the same number of rows (or columns) as lookup_array.
if_not_found. Optional text or value shown when there is no match, such as "Not found". It replaces the need for an IFERROR wrapper.
match_mode. Optional. 0 is exact (default), -1 exact or next smaller, 1 exact or next larger, 2 allows wildcards such as * and ?.
search_mode. Optional. 1 searches first to last, -1 searches last to first (useful for the latest entry), and 2 or -2 use binary search on sorted data.

Worked examples

Look up a price by SKU

	A	B	C	E	F
1	SKU	Product	Price	Look up	Price
2	A101	Desk lamp	24.99	A103
3	A102	Monitor stand	39
4	A103	USB hub	18.5

Formula: F2: =XLOOKUP(E2,A2:A4,C2:C4)

Result: 18.5

Searches A2:A4 and returns the item in the same position of C2:C4. Exact match is the default.

Custom message when missing

	A	B	D	E
1	SKU	Price	Look up	Price
2	A101	24.99	Z999
3	A102	39

Formula: E2: =XLOOKUP(D2,A2:A3,B2:B3,"Not in catalog")

Result: Not in catalog

The fourth argument replaces #N/A with your own text.

Look left of the key

	A	B
1	Employee	ID
2	Maria Lopez	1042
3	James Cole	1043
4	Priya Shah	1044

Formula: D2: =XLOOKUP(1043,B2:B4,A2:A4)

Result: James Cole

The return range (names) sits left of the search range (IDs). VLOOKUP cannot do this.

Try it

A tiny spreadsheet with example data. Edit the formula or any number and see the result.

Formula in F2: =XLOOKUP(103,A2:A6,B2:B6,"none")

Example spreadsheet for XLOOKUP
	A	B	C	D	E	F
1	ID	Item	Price	Stock		Result
2	101	Pen	2.5	120		Lamp
3	102	Notebook	4	80
4	103	Lamp	25	15
5	104	Mug	7.5	40
6	105	Backpack	39	12

Result in F2: Lamp

Practice this in a lesson: XLOOKUP

Common errors and how to fix them

#N/A. No exact match was found. Check for stray spaces or text-versus-number mismatches, or supply the if_not_found argument.
#VALUE!. The lookup range and the return range have different sizes (for example A2:A10 and C2:C11). Make them the same length.
#NAME?. Your Excel version does not have XLOOKUP (Excel 2019 and older). Use INDEX with MATCH instead.
Results shift after inserting rows. Keep both ranges the same size and use Table or full-column references so inserted rows are included.

Related functions

Frequently asked questions

Is XLOOKUP better than VLOOKUP?: For most jobs, yes: it is exact by default, looks in any direction, does not break when columns are inserted and has a built-in not-found message. VLOOKUP still matters for compatibility with older Excel.
Can XLOOKUP return several columns?: Yes. Give a multi-column return range such as B2:D10 and the result spills across the columns.
Does XLOOKUP work in Excel 2019?: No. It is available in Excel 2021, Excel 2024 and Microsoft 365.
How do I do an approximate match with XLOOKUP?: Set the fifth argument match_mode to -1 (exact or next smaller) or 1 (exact or next larger). No sorting is required.