Excel无FILTER函数时如何返回表格中指定值所有匹配行对应的ID

适用所有不支持FILTER函数的Excel版本，无需插件、VBA，纯函数组合即可实现需求。

预设范围说明 #

先确认你的表格对应区域，可根据实际数据调整：

• ID列为A列，第一条有效ID数据从第4行开始，最后一行数据为第100行（自行修改为实际行数）
• 需检索的数值列范围为B列到BC列
• 搜索值固定存放在B2单元格（对应示例中SEARCH行的检索值）

方案1：逐行输出匹配ID（下拉填充即可） #

在空白单元格输入以下公式，旧版Excel（2019及更早版本）输入后按Ctrl+Shift+Enter组合键确认生效，之后下拉填充即可依次列出所有匹配ID，无更多匹配结果时自动返回空值：

=IFERROR(INDEX($A:$A,SMALL(IF(COUNTIF(INDIRECT("B"&ROW($A$4:$A$100)&":BC"&ROW($A$4:$A$100)),$B$2)>0,ROW($A$4:$A$100),99999),ROW(A1))),"")

公式逻辑说明 #

• COUNTIF部分：逐行判断当前行的检索范围内是否存在指定搜索值，存在则返回大于0的结果
• IF部分：记录匹配行的行号，非匹配行填充无效大值99999
• SMALL部分：下拉时按顺序依次提取第1、2、3…个匹配行的行号
• INDEX部分：根据匹配行号提取对应ID
• IFERROR部分：匹配完所有结果后返回空值，屏蔽错误提示

方案2：合并输出为逗号分隔的ID字符串 #

如果需要直接得到「2、4、25」这类拼接好的结果，支持TEXTJOIN函数的版本可以使用以下公式，同样旧版需按Ctrl+Shift+Enter确认：

=TEXTJOIN("、",TRUE,IF(COUNTIF(INDIRECT("B"&ROW($A$4:$A$100)&":BC"&ROW($A$4:$A$100)),$B$2)>0,$A$4:$A$100,""))

无需下拉填充，单个单元格即可返回所有匹配ID的拼接结果。

大数据量优化建议 #

如果表格行数过万，可先将检索范围在名称管理器中定义为命名区域，能大幅提升公式运行效率，避免卡顿。

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