Excel无FILTER函数时用INDEX组合公式提取符合条件的所有行

一、原公式报错核心原因 #

• 语法错误：两处引用Data工作表时遗漏了感叹号!，分别出现在ROW('Data'$A$5:$A$124)和INDEX('Data'$A$5:$A$124,1,1)位置，修正后即可解决直接报错的问题
• 逻辑适配问题：原公式仅支持匹配Criteria!J15单个值，无法同时覆盖Chip 1、Chip 3、Chip 4三个筛选条件

二、正确公式配置步骤 #

前置确认 #

先明确筛选值的存放规则：如果3个筛选值分别存放在Criteria工作表J15、J16、J17单元格，直接使用下方标准公式即可；如果3个值合并存放在J15单个单元格（比如用逗号分隔），参考后续调整方案。

公式编写 #

假设输出表A1为表头行（对应食品名称、热量、蛋白质占比、脂肪占比4个字段），选中输出表A2单元格输入以下公式：

=INDEX('Data'!A:A,SMALL(IF(COUNTIF('Criteria'!$J$15:$J$17,'Data'!$A$5:$A$124)>0,ROW('Data'!$A$5:$A$124),99999),ROW(A1)))&""

注意：2019及更早版本的Excel，输入完公式后必须按Ctrl+Shift+Enter三键组合确认数组公式，直接回车会返回错误值。

操作规则 #

• 公式输入完成后，向右拉动填充到D列，覆盖全部4个字段
• 选中整行公式向下拉动填充，直到出现空白单元格为止，空白值代表已无符合条件的记录

特殊情况调整 #

如果3个筛选值全部合并存放在Criteria!J15单元格中，把公式里的COUNTIF('Criteria'!$J$15:$J$17,'Data'!$A$5:$A$124)>0替换为ISNUMBER(SEARCH('Data'!$A$5:$A$124,'Criteria'!$J$15))即可。

三、可调整参数说明 #

• 源数据范围变动：修改'Data'!$A$5:$A$124为实际的食品名称列数据范围即可
• 筛选值范围变动：修改'Criteria'!$J$15:$J$17为实际的筛选值存放范围即可

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