Excel多条件匹配返回指定值并生成无空连续列表的公式写法

需求判定规则 #

需要同时满足以下3个条件时，返回对应行A列的ID值，最终输出无空白行的连续结果列表：

• B列值等于Eligible/Previously Eligible
• C列日期距离当前日期间隔超过365天
• D列内容包含关键词10或20

原有公式问题排查 #

第一版数组公式失效原因 #

最初编写的数组公式直接返回全量A列内容，核心问题有2个：

1. D列匹配规则写错，原公式配置的是匹配*40*，和需求要求的*20*不符
2. COUNTIFS搭配横向常量数组{"*10*","*20*"}时会返回二维数组，未做聚合判断会导致条件判定恒真
   原错误公式参考：

=IFERROR(INDEX($A:$A,SMALL(IF((COUNTIFS($B:$B,"Eligible/Previously Eligible",$C:$C,"<"&TODAY()-365,$D:$D,{"*10*","*40*"})),ROW($A:$D)-MIN(ROW($A:$D))+1),ROW(A1)),COLUMN(A1)),"")

第二版逐行判断公式的局限 #

调整后的逐行判断公式可以正确识别单条符合条件的记录，但因为是逐行返回结果，不符合条件的行直接返回空值，自然会出现大量空白间隔，无法生成连续列表，且存在列引用笔误：日期判断列应为C列，原公式错写为D列。
原公式参考：

=IF(AND(B2="Eligible/Previously Eligible",D2<TODAY()-365,D2<>"",OR(SUM(COUNTIF(C2,{"*10*","*20*"})))),A2,"")

可用正确公式 #

根据自身使用的Excel版本选择对应写法，即可得到无空白的连续匹配结果：

适用于Excel 365/2021及以上版本（支持动态数组） #

直接在结果列第一个单元格输入以下公式，无需按数组组合键，公式会自动溢出所有连续结果：

=FILTER(A:A,(B:B="Eligible/Previously Eligible")*(C:C<TODAY()-365)*(ISNUMBER(SEARCH("10",D:D))+ISNUMBER(SEARCH("20",D:D)))*(A:A<>""),"")

适用于Excel 2019及更早版本（需数组确认） #

在结果列第一个单元格输入以下公式，按住CTRL+Shift+Enter三键确认数组公式后，下拉填充直到单元格返回空白即可：

=IFERROR(INDEX($A:$A,SMALL(IF(($B:$B="Eligible/Previously Eligible")*($C:$C<TODAY()-365)*(ISNUMBER(SEARCH("10",$D:$D))+ISNUMBER(SEARCH("20",$D:$D))*($A:$A<>"")),ROW($A:$A),9^9),ROW(A1))),"")

公式逻辑说明 #

• 用乘法符号*表示逻辑“与”，即所有条件同时满足时才会判定为真
• 用加法符号+表示逻辑“或”，即D列包含10或者包含20任意一个满足即可
• 用ISNUMBER+SEARCH组合实现单元格内容包含指定关键词的判定，比COUNTIF通配符写法在数组运算中更稳定，不会出现二维数组判定异常
• SMALL函数会从小到大提取所有符合条件的行号，配合INDEX逐行返回对应A列ID，不符合条件的行不会被纳入行号序列，因此不会出现空白间隔

小提示：如果你的数据存在表头，建议将公式中整列引用（如A:A、B:B）替换为实际数据所在的行范围（如A2:A1000、B2:B1000），可以大幅提升公式运算速度，同时避免表头内容被误判。

内容的提问来源于stack exchange，提问作者jillingworth