Excel 多条件查找：XLOOKUP 与 INDEX MATCH

=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7) 返回商品为 E2 并且 规格为 F2 的那一行的价格。每个比较都会检查每一行，两者相乘后只有都成立的行才是 1，XLOOKUP 查找的就是这个 1。它需要 Excel 2021 或 Microsoft 365；下面的 INDEX MATCH 写法适用于所有版本。

公式重置

G2fx

=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7)

	A	B	C	D	E	F	G
1	Product	Size	Price		Product	Size	Price
2	Coffee	Small	$2.50		Tea▾	Large▾	$3.00
3	Coffee	Large	$3.50
4	Tea	Small	$2.00
5	Tea	Large	$3.00
6	Juice	Small	$3.00
7	Juice	Large	$4.00

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

Tea 和 Large 在第 5 行相交，所以 G2 返回 $3.00。选择 Juice 和 Small：结果还是$3.00，但来自另一行。为没有任何行同时满足两个条件的情况加上第四个参数：=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7,"No such item")。

条件相乘的原理

A2:A7=E2 把每个商品与 E2 比较，返回六个 TRUE 或 FALSE。两个这样的列表相乘时，TRUE 变成 1，FALSE 变成 0，只有在两个列表中都是 1 的行结果才是 1。D 列显示的就是这个列表，由一个公式溢出得到。

公式重置

D2fx

=(A2:A7=F2)*(B2:B7=G2)

	A	B	C	D	E	F	G
1	Product	Size	Price	Both match		Product	Size
2	Coffee	Small	$2.50	0		Tea▾	Large▾
3	Coffee	Large	$3.50	0
4	Tea	Small	$2.00	0
5	Tea	Large	$3.00	1
6	Juice	Small	$3.00	0
7	Juice	Large	$4.00	0

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

只有 D5 是 1。修改 F2 或 G2，这个 1 就会移动。每增加一个条件，就多一个 *(range=value)，而且条件不一定是相等比较：*(C2:C7<3) 加上了“价格低于 3”。每个区域必须覆盖相同的行（A2:A7、B2:B7、C2:C7）：如果返回区域与条件区域大小不同，XLOOKUP 会返回 #VALUE!。

多条件的 INDEX MATCH

在 Excel 2019 及更早版本中，可以让 MATCH 在同样的数组中查找 1，再由 INDEX 返回该位置的价格。

公式重置

G2fx

=INDEX(C2:C7,MATCH(1,(A2:A7=E2)*(B2:B7=F2),0))

	A	B	C	D	E	F	G
1	Product	Size	Price		Product	Size	Price
2	Coffee	Small	$2.50		Coffee	Large	$3.50
3	Coffee	Large	$3.50
4	Tea	Small	$2.00
5	Tea	Large	$3.00
6	Juice	Small	$3.00
7	Juice	Large	$4.00

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

Coffee 和 Large 是数组中的第 2 个位置，INDEX 返回 $3.50。在 Excel 2019 及更早版本中，这是一个数组公式：要按 Ctrl+Shift+Enter（Mac 上按 Cmd+Shift+Enter）而不是 Enter，Excel 会把它显示在花括号中。在那些版本里直接按 Enter 通常会返回 #N/A 或 #VALUE!。在 Excel 365 中按 Enter 就够了。单条件的写法见 INDEX 和 MATCH 页面。

把多个条件拼成一个键

另一种方法是把两个条件拼接成一个。VLOOKUP 需要把拼接后的值放在表格开头的辅助列中（VLOOKUP 页面介绍了这种写法）。XLOOKUP 可以在公式内部拼接区域，所以不需要辅助列。

公式重置

G2fx

=XLOOKUP(E2&"|"&F2,A2:A7&"|"&B2:B7,C2:C7)

	A	B	C	D	E	F	G
1	Product	Size	Price		Product	Size	Price
2	Coffee	Small	$2.50		Juice	Large	$4.00
3	Coffee	Large	$3.50
4	Tea	Small	$2.00
5	Tea	Large	$3.00
6	Juice	Small	$3.00
7	Juice	Large	$4.00

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

A2:A7&"|"&B2:B7 构造出六个键，例如 Juice|Large，XLOOKUP 在其中找到 Juice|Large：$4.00。各部分之间要放一个分隔符。不加分隔符的话，“AB”和“C”拼起来的“ABC”与“A”和“BC”拼起来的一样，查找就可能返回错误的行。

如果要找的值是数字，并且每种组合只出现一次，SUMIFS 完全不用数组也能给出同样的答案：=SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2)。没有匹配时它返回 0 而不是错误，这可能会掩盖拼写错误。

用 FILTER 返回所有匹配项

XLOOKUP 和 INDEX MATCH 返回第一个匹配的行。如果有多行匹配并且你想要全部，就用 FILTER 配合同样的条件。

公式重置

F2fx

=FILTER(C2:D8,(A2:A8="North")*(B2:B8="Phone"))

	A	B	C	D	E	F	G
1	Region	Product	Quarter	Sales		Matches
2	North	Laptop	Q1	120		Q1	210
3	North	Phone	Q1	210		Q2	225
4	South	Laptop	Q1	95		Q3	240
5	North	Phone	Q2	225
6	South	Phone	Q2	180
7	North	Laptop	Q2	135
8	North	Phone	Q3	240

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

有三行同时是 North 和 Phone，所以 F2 把它们的季度和销售额溢出到 F2:G4。把 A3 改成 South，列表就缩减为两行。没有任何行匹配时，FILTER 返回 #CALC!；加上第三个参数，例如 "None"，就会显示文本。更多用法见 FILTER 页面。

练习：三个条件

公式重置

G4fx

	A	B	C	D	E	F	G
1	Region	Product	Quarter	Sales		Region	South
2	North	Laptop	Q1	120		Product	Laptop
3	North	Laptop	Q2	135		Quarter	Q2
4	North	Phone	Q1	210		Sales
5	South	Laptop	Q1	95
6	South	Phone	Q2	180
7	South	Laptop	Q2	110
8	North	Phone	Q2	225

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

轮到你了： 在 G4 中返回地区为 G1、商品为 G2、季度为 G3 的销售额。

检查显示答案