XLOOKUP function

XLOOKUP syntax

Argument	Required	What it means
lookup_value	Yes	What you're looking for, usually a cell such as E2.
lookup_array	Yes	The single column or row to search in.
return_array	Yes	The column or row to bring back a value from. Same height as lookup_array.
if_not_found	No	What to show when there's no match. Without it you get #N/A.
match_mode	No	0 exact (the default), -1 exact or next smaller, 1 exact or next larger, 2 wildcards.
search_mode	No	1 searches first to last (the default), -1 last to first.

Examples

Find a price by product code

The code to find is in E2. XLOOKUP searches the codes in column A and returns the price on the same row from column C.

F2fx=XLOOKUP(E2,A2:A6,C2:C6)

Row	A	B	C	D	E	F
1	Code	Product	Price		Find	Price
2	A-101	Keyboard	$49.00		A-103	$189.00
3	A-102	Mouse	$19.00
4	A-103	Monitor	$189.00
5	A-104	Webcam	$59.00
6	A-105	Headset	$79.00

=XLOOKUP(E2,A2:A6,C2:C6) → $189.00Copy

Show a message instead of #N/A

A-199 isn't in the list. The fourth argument, if_not_found, decides what appears instead of the #N/A error.

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

Row	A	B	C	D	E	F
1	Code	Product	Price		Find	Price
2	A-101	Keyboard	$49.00		A-199	Not found
3	A-102	Mouse	$19.00
4	A-103	Monitor	$189.00
5	A-104	Webcam	$59.00
6	A-105	Headset	$79.00

=XLOOKUP(E2,A2:A6,C2:C6,"Not found") → Not foundCopy

Look to the left

VLOOKUP can only return columns to the right of the one it searches. XLOOKUP takes any return range, so finding the code for a product name, one column to the left, is the same formula shape.

F2fx=XLOOKUP(E2,B2:B6,A2:A6)

Row	A	B	C	D	E	F
1	Code	Product	Price		Find	Code
2	A-101	Keyboard	$49.00		Webcam	A-104
3	A-102	Mouse	$19.00
4	A-103	Monitor	$189.00
5	A-104	Webcam	$59.00
6	A-105	Headset	$79.00

=XLOOKUP(E2,B2:B6,A2:A6) → A-104Copy

Find the band a number falls in

Commission rates start at each threshold in column A. With match_mode -1, XLOOKUP returns the exact match or the next smaller threshold, so sales of 32,500 get the rate that starts at 25,000. The thresholds don't need sorting.

E2fx=XLOOKUP(D2,A2:A5,B2:B5,,-1)

Row	A	B	C	D	E
1	Sales from	Rate		Sales	Rate
2	0	2%		32,500	6%
3	10,000	4%
4	25,000	6%
5	50,000	8%

=XLOOKUP(D2,A2:A5,B2:B5,,-1) → 6%Copy

Practise it

New data, so you can't copy the answer above. We check the number your formula returns, then change the data to make sure it still holds.

Your task: In F2, return the department of the employee whose ID is in E2.

Graded live

F2fx

Row	A	B	C	D	E	F
1	ID	Name	Department		ID	Department
2	1041	Ana Kola	Finance		1077
3	1052	Ben Ortiz	Sales
4	1063	Chloe Park	Marketing
5	1077	Dev Shah	Operations
6	1089	Eva Lind	Sales
7	1094	Femi Ade	Finance

Write the formula in the bar above, then press Check or Enter.

Hint

Search the IDs in A2:A7 for the value in E2, and return from the department column, C2:C7.

Solution

=XLOOKUP(E2,A2:A7,C2:C7) Any formula that returns the same result passes.

Check

Common mistakes

1. Ranges of different sizes. A2:A6 and C2:C7 don't line up, so XLOOKUP returns #VALUE!. Make both ranges start and end on the same rows.
2. Expecting a blank instead of #N/A. With no match and no if_not_found, the result is #N/A. Add a fourth argument, such as "" or "Not found".
3. Ranges that move when you copy the formula down. Copy =XLOOKUP(E2,A2:A6,C2:C6) one row down and the ranges become A3:A7 and C3:C7. Lock them with $: $A$2:$A$6.
4. Using it where it doesn't exist. Excel 2019 and older show #NAME? for XLOOKUP. If your file must open there, use INDEX and MATCH instead.

XLOOKUP vs VLOOKUP and INDEX/MATCH

	XLOOKUP	VLOOKUP	INDEX + MATCH
Default match	Exact	Approximate (unless you add FALSE)	Depends on MATCH’s last argument
Return column to the left	Yes	No	Yes
Survives inserted columns	Yes	No, the column number goes stale	Yes
Built-in “not found” value	Yes	No, wrap it in IFNA	No, wrap it in IFNA
Works in Excel 2019 and older	No	Yes	Yes

If everyone who opens the file has Microsoft 365 or Excel 2021, use XLOOKUP. If not, INDEX and MATCH does the same job everywhere.