如何用Excel的INDEX MATCH函数返回多个匹配值？

针对你用INDEX+MATCH只能返回第一个匹配值的问题，以下是不同Excel版本的可行方案：

方案1：Excel 365/2021（动态数组版本） #

方法A：FILTER + TEXTJOIN（最简洁） #

直接筛选目标学院的所有学生，再用逗号连接：

=TEXTJOIN(", ", TRUE, FILTER(Data!B:B, Data!A:A=lookup!E2, ""))

• FILTER(Data!B:B, Data!A:A=lookup!E2, "")：提取Data表中与lookup表E2学院匹配的所有学生姓名，无匹配时返回空
• TEXTJOIN(", ", TRUE, ...)：用「逗号+空格」连接筛选结果，TRUE参数自动忽略空值

方法B：INDEX + SMALL + TEXTJOIN（兼容INDEX逻辑） #

如果习惯用INDEX的思路，可结合SMALL提取所有匹配行的位置：

=TEXTJOIN(", ", TRUE, INDEX(Data!B:B, SMALL(IF(Data!A:A=lookup!E2, ROW(Data!A:A)-MIN(ROW(Data!A:A))+1), ROW(INDIRECT("1:"&COUNTIF(Data!A:A, lookup!E2))))))

• IF(Data!A:A=lookup!E2, ROW(Data!A:A)-MIN(ROW(Data!A:A))+1)：生成匹配行的相对位置
• SMALL(..., ROW(INDIRECT("1:"&COUNTIF(...))))：依次提取第1、2...N个匹配位置
• INDEX根据位置取对应姓名，最后用TEXTJOIN连接

方案2：Excel 2019及更早版本（非动态数组） #

需输入数组公式后按Ctrl+Shift+Enter确认（不能直接回车）：

=TEXTJOIN(", ", TRUE, IF(Data!A:A=lookup!E2, Data!B:B, ""))

注意：Excel 2016及以后才支持TEXTJOIN函数，若用2013及更早版本，可改用自定义VBA函数或嵌套INDEX+SMALL的复杂数组公式（效率较低，不推荐）。

额外优化建议 #

• 避免整列引用（如Data!B:B），改用实际数据范围（如Data!B2:B100），提升公式计算效率
• 若需去重，365版本可添加UNIQUE函数：

  =TEXTJOIN(", ", TRUE, UNIQUE(FILTER(Data!B:B, Data!A:A=lookup!E2, "")))
  

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