Excel中INDEX、SMALL、IF数组公式一个有效另一个失效问题求助

算力阿峰

2026-10-17

Excel数组公式筛选无结果问题排查

场景说明

你有3个工作表，分别为Data（源数据表）、Full_Questions（第一次查询结果表）、FGD - Pop（待生成的筛选结果表）。

Check (A)	Sector (B)	Sub/Sector(C)	Overarching Question (D)	Q# (E)	Questions (F)	BHA_Tool (G)
True	S1	Sector:A	Is this a new methodology, tool, or technique related to agricultural production?	S1q1	Is this a new methodology, tool, or technique related to agricultural production?	6 Other Source
True	S1	Sector:A	Do the targeted participants have experience in the proposed activity?	S1q2	What are the types of post-production processes your household currently utilizes	3 FGD - Pop
True	S1	Sector:A	Do the targeted participants have experience in the proposed activity?	S1q3	What are the types of grain storage your household currently uses	3 FGD - Pop

你在Full_Questions表中用以下数组公式（旧版Excel输入后按Ctrl+Shift+Enter生效）查询Data表中A列为True的F列内容，运行正常：

{=IFERROR(INDEX(Data!F$2:F$300,SMALL(IF(Data!A$2:A$300=TRUE,ROW(Data!F$2:F$300)),ROW(1:1))-1,1),"")}

Full List of Q (A)	To Be asked of... (B)	Available in tool.. (C)
Is this a new methodology, tool, or technique related to agricultural production?	Project Documents	6 Other Source
What are the types of post-production processes your household currently utilizes	Farming Households; disag by gender, age range, etc	3 FGD - Pop
What are the types of grain storage your household currently uses	Farming Households; disag by gender, age range, etc	3 FGD - Pop

你写的两个失败公式分别存在以下问题：

从Full_Questions表查询的公式：大概率是C列存在不可见空格、换行符，导致字符串匹配失败，另外要确认旧版Excel输入数组公式时按了Ctrl+Shift+Enter，而非普通回车。
直接从Data表多条件查询的公式：AND函数不支持数组运算，它会把所有条件的结果合并返回单个布尔值，而不是每一行对应的布尔值数组，因此IF判断永远只能拿到一个结果，无法筛选符合条件的行。

把多条件的AND改为乘法运算实现数组级的多条件判断，公式如下（旧版Excel输入后按Ctrl+Shift+Enter，365/2021版直接回车即可自动溢出）：

{=IFERROR(INDEX(Data!F$2:F$300,SMALL(IF((Data!A$2:A$300=TRUE)*(Data!G$2:G$300="3 FGD - Pop"),ROW(Data!F$2:F$300)-1,""),ROW(1:1)),1),"")}

这里也把-1的逻辑移到了ROW计算后，避免后续行号偏移出错。

在匹配前加上TRIM函数去除单元格前后的不可见空格，避免匹配失败：

{=IFERROR(INDEX(Full_Questions!A$2:A$300,SMALL(IF(TRIM(Full_Questions!C$2:C$300)="3 FGD - Pop",ROW(Full_Questions!A$2:A$300)-1,""),ROW(1:1)),1),"")}

如果使用Excel 365/2021版本，可以直接用更简单的FILTER函数实现，不需要数组公式特殊回车操作：

=FILTER(Data!F:F,(Data!A:A=TRUE)*(Data!G:G="3 FGD - Pop"),"")

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