Excel实现多匹配结果索引查找，FILTER函数报错#SPILL!如何解决

你当前遇到的问题来自现有公式的两个设计缺陷：

• FILTER函数的返回范围是List_State_11.18.2021!A2:R81，即匹配到结果时会返回整行18列数据，哪怕仅1条匹配结果也会自动向右侧17个单元格溢出，只要右侧单元格有内容就会触发*#SPILL!*错误
• 没有对多匹配结果做合并处理，多个匹配结果会向下/向右溢出，无法输出到单个单元格

方案1：Excel 365/2021及以上版本（推荐） #

使用TEXTJOIN函数包裹FILTER的输出，直接将所有匹配的故障内容合并到当前单元格，完全避免溢出问题，公式如下：

=TEXTJOIN("；",TRUE,IFERROR(FILTER(List_State_11.18.2021!J2:J81,List_State_11.18.2021!A2:A81=FinalResult!A2,""),"No results"))

参数说明：

• 第一个参数"；"是多个故障内容的分隔符，你可以替换为CHAR(10)，同时打开单元格自动换行，实现每个故障单独占一行的效果
• 第二个参数TRUE代表自动跳过空值，避免无意义的分隔符
• FILTER仅返回J列的故障文本，不会产生多列溢出
• 外层IFERROR用于兜底异常场景，确保无匹配时正常返回提示文本

方案2：Excel 2019及更低版本 #

低版本Excel无FILTER、TEXTJOIN函数，可使用数组公式实现多结果依次输出：

=IFERROR(INDEX(List_State_11.18.2021!J:J,SMALL(IF(List_State_11.18.2021!A$2:A$81=A2,ROW($2:$81),99999),ROW(A1)))&"","No results")

使用说明：

• 输入公式后需要按Ctrl+Shift+Enter组合键激活数组公式
• 下拉公式可依次输出同一设备的多条故障记录，无匹配时返回提示文本
• 若需要将多结果合并到同一单元格，需通过VBA编写自定义函数实现

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