XLOOKUP with multiple criteria

In short: =XLOOKUP(E2&F2,A2:A9&B2:B9,C2:C9) matches item and size together. For any number of conditions, including comparisons, use =XLOOKUP(1,(A2:A9=E2)*(B2:B9=F2),C2:C9).

Try it now

The notepad comes in three sizes and each has its own price. A plain XLOOKUP on the item name stops at the first notepad in the list, which is the wrong size.

Task The notepad comes in several sizes. In G2, use XLOOKUP to find the price of the item in E2 in the size given in F2.

	A	B	C	D	E	F	G
1	Item	Size	Price $		Item	Size	Price
2	Notepad	A5	1.9		Notepad	A4
3	Binder	A4	3.49
4	Clipboard	A4	4.2
5	Notepad	A4	2.6
6	Binder	A5	2.99
7	Notepad	A6	1.2
8	Clipboard	A5	3.1
9	Spiral pad	A4	3.8

Method 1: join the lookup values and the lookup arrays

This is the classic answer to "XLOOKUP with two criteria". Glue the lookup values together with & and glue the lookup columns together the same way. Excel then builds one combined key per row and compares it with yours.

Argument	What goes in	In the example
lookup_value	Both lookup values joined	E2&F2 becomes "NotepadA4"
lookup_array	Both lookup columns joined row by row	A2:A9&B2:B9 becomes "NotepadA5", "BinderA4" and so on
return_array	The column holding the answer	C2:C9
if_not_found (optional)	Text to show when no row matches	"not found"

A third criterion just extends both chains: =XLOOKUP(F2&G2&H2,A2:A9&B2:B9&C2:C9,D2:D9). Keep the order identical on both sides.

Add a delimiter to avoid false matches

Joined keys can collide now and then: "AB" plus "C" gives the same key as "A" plus "BC". If your data could look like that, put a character between the parts that never appears in the data: =XLOOKUP(E2&"|"&F2,A2:A9&"|"&B2:B9,C2:C9).

Method 2: multiply conditions and look up 1

The boolean method looks odd the first time, but it is the more flexible one. Each condition in brackets produces TRUE or FALSE for every row. When you multiply the conditions, Excel treats TRUE as 1 and FALSE as 0, so only a row where every condition holds ends up as 1. XLOOKUP then searches for that 1.

=XLOOKUP(1,(A2:A9=F2)*(B2:B9=G2)*(C2:C9=H2),D2:D9)

Because each condition is an ordinary comparison, you are not limited to "equals". Something like (D2:D9>=1000) or a date window with two conditions works fine, which the join method cannot do. Try it with three criteria:

Task Three criteria: in I2, return the sales for the region in F2, the product in G2 and the quarter in H2. Use the method with 1 and multiplied conditions.

	A	B	C	D	E	F	G	H	I
1	Region	Product	Quarter	Sales $		Region	Product	Quarter	Sales
2	South	Toner	Q2	2350		North	Toner	Q2
3	North	Paper	Q1	1800
4	North	Toner	Q2	2900
5	South	Paper	Q1	1450
6	North	Paper	Q2	2050
7	South	Toner	Q1	2600
8	North	Toner	Q1	3100
9	South	Paper	Q2	1700

When the combination does not exist

If no row satisfies every condition, XLOOKUP returns #N/A. The fourth argument handles that for both methods, so there is no need to wrap the formula in IFERROR.

Task In G2, return the sales of the person in E2 for the month in F2. March is still missing for Lena Hoffmann. If the combination does not exist, G2 should show “not found” instead of #N/A.

	A	B	C	D	E	F	G
1	Name	Month	Sales $		Name	Month	Sales
2	Murat Kaya	February	4200		Lena Hoffmann	March
3	Jana Weber	January	3850
4	Lena Hoffmann	February	5100
5	Murat Kaya	January	3900
6	Jana Weber	March	4600
7	Lena Hoffmann	January	4450
8	Murat Kaya	March	4750
9	Jana Weber	February	3700

Which method should you use?

	Join with &	Multiply and look up 1
Readability	short and easy to follow	a little longer
Only "equals"	yes	no, also >, < and date ranges
Number vs text	no problem, & turns everything into text	5 and "5" do not match
Risk of false matches	rare, avoidable with a delimiter	none

Common mistakes

#VALUE! instead of a result

All ranges must have the same size. Mixing A2:A9&B2:B10, or pairing them with a return array of C2:C8, gives #VALUE!.

#N/A although the row is clearly there

The usual suspects are a trailing space or a number stored as text. TRIM removes the spaces, see SUBSTITUTE and TRIM. With the boolean method, also check that the lookup value and the column are both real numbers.

Several rows match

XLOOKUP returns the first match from the top. For the last one, set search_mode to -1: =XLOOKUP(E2&F2,A2:A9&B2:B9,C2:C9,"not found",0,-1). If you need every match or their total, FILTER or SUMIFS is the better tool.

Whole columns slow the file down

A:A&B:B joins more than a million rows on every recalculation. Use fixed ranges or an Excel table (Ctrl+T), whose ranges grow with the data.

XLOOKUP with multiple criteria in German Excel

German Excel calls the function XVERWEIS and separates arguments with semicolons. The & operator and the multiplication stay exactly the same.

English	German
=XLOOKUP(E2&F2,A2:A9&B2:B9,C2:C9)	=XVERWEIS(E2&F2;A2:A9&B2:B9;C2:C9)
=XLOOKUP(1,(A2:A9=E2)*(B2:B9=F2),C2:C9)	=XVERWEIS(1;(A2:A9=E2)*(B2:B9=F2);C2:C9)

Which Excel versions support this?

XLOOKUP is available in Microsoft 365, Excel 2021 and Excel 2024. Excel 2019 and older do not have it and show #NAME?. In every version that has XLOOKUP, both methods work as normal formulas without Ctrl+Shift+Enter, because those versions support dynamic arrays.

For older versions, use =INDEX(C2:C9,MATCH(1,(A2:A9=E2)*(B2:B9=F2),0)) and in Excel 2019 and earlier confirm it as a legacy array formula with Ctrl+Shift+Enter. The helper column approach is covered in VLOOKUP with multiple criteria, and the basics of the function in XLOOKUP.

FAQ