夜雨聆风VLOOKUP只能返回第一个匹配值，遇到“一对多”就歇菜。本文分享3种经典方案：TEXTJOIN+IF（合并返回）、FILTER函数（动态数组，一键筛选）、INDEX+SMALL+IF（万能数组公式）。从合并显示到动态筛选，总有一款适合你的场景！
Excel 3种经典的“一对多查询”方案，VLOOKUP做不到的它们能
根据“销售部”找出所有员工，VLOOKUP只能返回第一个。一对多查询，需要换思路！
一、什么是一对多查询？
场景举例：
根据部门，列出所有员工姓名
根据产品类别，列出所有产品
根据订单号，列出所有明细行
VLOOKUP的局限： 找到第一个匹配值就停止，无法返回第2、3、4个
二、3种经典方案
方案1：TEXTJOIN+IF（合并到一个单元格）
适用场景：将所有匹配结果合并到一个单元格，用分隔符隔开
公式：=TEXTJOIN("、", TRUE, IF(部门列=部门单元格, 姓名列, ""))
操作步骤：
输入公式（Excel 2019及以上版本）
按
Ctrl+Shift+Enter（数组公式三键确认）向下填充
示例： 查询“销售部”所有员工结果：张三、李四、王五
优点： 一目了然，适合打印/汇报缺点： 所有结果挤在一个单元格
方案2：FILTER函数（动态数组，自动溢出）
适用场景：将结果动态显示在多个单元格中，新版Excel首选
公式：=FILTER(姓名列, 部门列=部门单元格, "无数据")
操作步骤（Excel 365/2021）：
在单元格输入公式
直接回车（无需三键）
结果自动溢出到下方单元格
示例： 查询“销售部”所有员工结果：C2显示“张三”，C3显示“李四”，C4显示“王五”
优点： 动态更新，结果分行显示缺点： 需要Excel 365或2021版本
方案3：INDEX+SMALL+IF（万能数组公式）
适用场景：所有Excel版本通用，结果分行显示
公式：=IFERROR(INDEX(姓名列, SMALL(IF(部门列=部门单元格, ROW(部门列)-起始行+1, ""), ROW(A1))), "")
操作步骤：
输入公式
按
Ctrl+Shift+Enter向下拖动直到出现空白
公式解析：
IF：判断部门是否匹配，返回行号
SMALL：依次提取第1、2、3小的行号
INDEX：根据行号返回姓名
IFERROR：超出范围时显示空白
优点： 所有Excel版本通用，结果分行显示缺点： 公式复杂，大数据量时较慢
三、3种方案如何选择？
方案1：TEXTJOIN+IF
结果显示：合并到一个单元格
公式复杂度：简单
Excel版本：2019及以上
推荐场景：汇报展示（所有结果放一起）
方案2：FILTER
结果显示：溢出到多行
公式复杂度：最简单
Excel版本：365/2021
推荐场景：新版Excel首选
方案3：INDEX+SMALL+IF
结果显示：拖动填充到多行
公式复杂度：复杂
Excel版本：所有版本
推荐场景：旧版Excel必备
四、实战案例
案例1：根据班级列出所有学生
数据：A列班级，B列姓名
公式（FILTER版本）：=FILTER(B:B, A:A="一班", "")
案例2：根据订单号列出所有产品明细
数据：订单号、产品名称、数量
需求：查询“ORD001”的所有产品
公式（TEXTJOIN版本）：=TEXTJOIN("、", TRUE, IF(A:A="ORD001", B:B, ""))
案例3：旧版Excel通用方案
使用INDEX+SMALL+IF，适用于Excel 2016及以下版本
五、常见问题
问题1：TEXTJOIN+IF返回空白？原因：未按数组三键确认（Ctrl+Shift+Enter）解决：编辑公式后按三键
问题2：FILTER函数报错？原因：Excel版本过低解决：升级到365/2021，或使用INDEX+SMALL+IF
问题3：INDEX+SMALL+IF拖出很多空白行？原因：正常现象，公式需要预留足够行数解决：IFERROR会自动显示空白，无需处理
六、其他辅助函数（365专属）
UNIQUE去重后再查询：=UNIQUE(FILTER(B:B, A:A="销售部", ""))
SORT排序后查询：=SORT(FILTER(B:B, A:A="销售部", ""))
七、总结要点
如果是Excel 365/2021用户：首选 FILTER 函数，最简单高效
如果是Excel 2019用户：日常用 TEXTJOIN+IF，需要分行显示用方案3
如果是Excel 2016及以下用户：必学 INDEX+SMALL+IF，通用万能
VLOOKUP不行，换这些方案，一对多查询不再是难题！
