1. Excel 公式
=LOOKUP(1,1/((条件1)*(条件2)*……*(条件N)),目标列)
2. 公式举例与解析
2.1. 公式举例
在线文档示例: 点击查看
2.2. 公式解析
-
条件部分:
(A:A=E4)*(B:B=F4)
A:A=E4
:这个部分会生成一个逻辑数组,其中每个元素表示A
列中的相应单元格是否等于E4
。如果相等,则对应位置返回TRUE
(或1),否则返回FALSE
(或0)。B:B=F4
:这个部分生成一个逻辑数组,其中每个元素表示B
列中的相应单元格是否等于F4
。同样,如果相等,则返回TRUE
(或1),否则返回FALSE
(或0)。(A:A=E4)*(B:B=F4)
:这两个数组相乘会生成一个新的数组,只有在两个条件都为TRUE
的情况下,结果才会是 1,否则为 0。
-
除法部分:
1/((A:A=E4)*(B:B=F4))
- 生成的逻辑数组中,满足条件的元素位置会得到
1
,其余的会得到0
。 1/
将 1 转换为1
,将 0 转换为#DIV/0!
(除以零错误)。
- 生成的逻辑数组中,满足条件的元素位置会得到
-
LOOKUP 函数:
LOOKUP(1,1/((A:A=E4)*(B:B=F4)),C:C)
LOOKUP
函数会忽略错误值#DIV/0!
并尝试找到一个接近于1
的数值。- 因为数组中只有
1
和#DIV/0!
,所以LOOKUP
函数会找到最后一个1
的位置。 - 在找到的这个位置上,返回
C
列相应位置的值。
此处,C5
和C10
都是接近于 1
的数值,但LOOKUP
函数会返回最后一个位置的值,即C10
的值6,579.99
。
3. 注意事项
3.1. 返回值有多个时,默认返回最后一个
在使用LOOKUP
函数进行多条件匹配时,需要注意如果有多个返回值时,LOOKUP
函数会返回最后一个位置的值。
如果该返回值不符合您的预期,您需要对原始数据进行事先整理,或使用其他方法,具体实现方法详见下文“4. 常见问题”。
4. 常见问题
4.1. 多条件匹配获取第一行
可以考虑在原始数据中,插入一列序号
,并填入1、2、3、……
,然后按照序号
列倒序排序后,再使用LOOKUP
进行多条件匹配。
4.2. 多条件匹配最大值
使用MAXIFS
函数,如下:
=MAXIFS(C:C,A:A,E4,B:B,F4)
4.3. 多条件匹配最小值
使用MINIFS
函数,如下:
=MINIFS(C:C,A:A,E4,B:B,F4)
4.4. 多条件求和
使用SUMIFS
函数,如下:
=SUMIFS(C:C,A:A,E4,B:B,F4)
4.5. 多条件求平均值
使用AVERAGEIFS
函数,如下:
=AVERAGEIFS(C:C,A:A,E4,B:B,F4)
4.6. 处理错误值
如果LOOKUP
多条件匹配不到任何值,会返回错误值#N/A
,可以使用IFERROR
函数优化公式的返回值,举例如下:
=IFERROR(LOOKUP(1,1/((A:A=E4)*(B:B=F4)),C:C),"无")
5. 更多信息
- LOOKUP 函数:https://support.microsoft.com/zh-cn/office/lookup-%E5%87%BD%E6%95%B0-446d94af-663b-451d-8251-369d5e3864cb
- MAXIFS 函数:https://support.microsoft.com/zh-cn/office/maxifs-%E5%87%BD%E6%95%B0-dfd611e6-da2c-488a-919b-9b6376b28883
- MINIFS 函数:https://support.microsoft.com/zh-cn/office/minifs-%E5%87%BD%E6%95%B0-6ca1ddaa-079b-4e74-80cc-72eef32e6599
- SUMIFS 函数:https://support.microsoft.com/zh-cn/office/sumifs-%E5%87%BD%E6%95%B0-c9e748f5-7ea7-455d-9406-611cebce642b
- AVERAGEIFS 函数:https://support.microsoft.com/zh-cn/office/averageifs-%E5%87%BD%E6%95%B0-48910c45-1fc0-4389-a028-f7c5c3001690
- IFERROR 函数:https://support.microsoft.com/zh-cn/office/iferror-%E5%87%BD%E6%95%B0-c526fd07-caeb-47b8-8bb6-63f3e417f611