The XLOOKUP function in Excel

Q: Can XLOOKUP return more than one column?

Yes. If return_array spans several columns, like B2:D6, the whole matching row spills into the cells to the right.

XLOOKUP finds a value in one column and hands back the matching item from another column, in any direction. It's the modern replacement for VLOOKUP: exact match is the default, it can look left, and it has a built-in message for missing values. Reach for it whenever you need to pull a price, owner or status from a reference table.

Syntax

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

lookup_value	The value to find, usually a cell like `F2`.
lookup_array	The single column (or row) to search in.
return_array	The column (or row) to return a value from. It must be the same height as `lookup_array`.
if_not_found	Optional. What to show when there's no match, like `"Not found"`. Without it you get #N/A.
match_mode	Optional. `0` exact match (default), `-1` exact or next smaller, `1` exact or next larger, `2` wildcard match with `*` and `?`.
search_mode	Optional. `1` searches top to bottom (default), `-1` searches bottom to top, which is handy for finding the latest entry.

Try it yourself

This is a live spreadsheet. Type the formula and press Enter.

In G2, use XLOOKUP to find the Order ID for the customer named in F2. Notice the Order ID column is to the left of the customer column.

	A	B	C	D	F	G
1	Order ID	Customer	Carrier	Status	Customer	Order ID
2	ORD-1041	Harbor Café	DHL	Delivered	Brightway Dental
3	ORD-1042	Lumen Studio	UPS	In transit
4	ORD-1043	Pine & Co	FedEx	Delivered
5	ORD-1044	Brightway Dental	UPS	Processing
6	ORD-1045	Kestrel Labs	DHL	In transit
7	ORD-1046	Oak Street Books	FedEx	Delivered
8
9
10

Tip: Search the customer column (B) and return from the Order ID column (A).

Examples

=XLOOKUP("P-204",A2:A6,D2:D6)→ 489

Searches the SKU column for P-204 and returns the price from the same row.

=XLOOKUP("Monitor arm",B2:B6,A2:A6)→ "P-203"

Looks up a product name and returns its SKU, which sits to the left. VLOOKUP can't do this.

=XLOOKUP("P-299",A2:A6,B2:B6,"Not in catalog")→ "Not in catalog"

P-299 doesn't exist, so the fourth argument replaces #N/A with a readable message.

Example data

1	SKU	Product	Category	Price	Stock
2	P-201	Desk lamp	Lighting	34.5	12
3	P-202	Ergonomic chair	Seating	219	4
4	P-203	Monitor arm	Accessories	79	0
5	P-204	Standing desk	Desks	489	7
6	P-205	Cable tray	Accessories	18	25

Common mistakes

Ranges of different sizes
lookup_array and return_array must cover the same rows. A2:A6 with D2:D7 gives #VALUE!. Make both ranges start and end on the same rows.
Number stored as text
An ID typed as text ("1001") won't match the number 1001, so you get #N/A. Make both sides the same type, for example by converting the column with VALUE.
Expecting VLOOKUP-style approximate matching
XLOOKUP is exact by default. For tiered tables like tax bands or discounts, set match_mode to -1 to get the next smaller value.
Ranges shift when copied down
Lock the lookup and return ranges with $, as in $A$2:$A$6, before filling the formula down a column.

FAQ

Is XLOOKUP better than VLOOKUP?+

For new work, usually yes. It defaults to an exact match, can return columns on either side, and doesn't break when someone inserts a column. VLOOKUP is still worth knowing because older files and many interview tests use it.

Which Excel versions have XLOOKUP?+

Microsoft 365, Excel 2021 and later, and Excel for the web. In Excel 2019 or older it shows #NAME?, so use INDEX/MATCH there.

Can XLOOKUP return more than one column?+

Yes. If return_array spans several columns, like B2:D6, the whole matching row spills into the cells to the right.

Lesson

Master XLOOKUP step by step: XLOOKUP

Look up in any direction with XLOOKUP and show a friendly message when nothing matches.

Related functions

VLOOKUP INDEX MATCH IFERROR