一对多查找，一直是 Excel 迷们用来比拼水平高下的一个永恒习题 。前不久 ， 我也刚以此为案例讲解过万金油公式的用法 。
今天要教大家的是一对多查找的另一种解决办法 。
案例：
下表 1 是原始表格，要求只要列出班级名称，就能将全班所有人的信息提取到另一张表中 。效果如下图 2 。
解决方案：
先在 E1 单元格制作班级下拉菜单：1. 选中 E1 单元格 --> 选择菜单栏的“数据”-->“数据验证”
2. 在弹出的对话框中进行以下设置 --> 点击“确定”：
- 允许：选择“序列”
- 来源：输入“一班,二班,三班”
现在，班级下拉菜单就做好了 。
3. 将 D 列用作辅助列，在 D2 单元格输入以下公式，下拉复制公式：
=COUNTIF($A$2:A2,$E$1)
这个公式的作用是统计下拉菜单中的班级在 A 列中第几次出现 。
关于 countif 函数用法详解 ， 请参加【】
4. 在 E2 单元格输入以下公式，右拉下拉复制公式：
=IFERROR(INDEX(A$2:A$10,MATCH(ROW(A1),$D$2:$D$10,0)),"")
关于 index match 组合函数的详解，请参见【除了 vlookup，这几个经典组合公式基本可以解决所有查找问题】
公式释义：
- INDEX(查找的区域，区域中的第几行)
- A$2:A$10：查找的区域，区域的行值是固定的，需要绝对引用；列值要相对引用，这样向右拖动公式的时候，列数会自动递增，即依次取 B 列、C 列的值
- MATCH(搜索的项,搜索区域,绝对匹配)：返回搜索项在区域中的相对位置
- ROW(A1)：A1 的行号，即“1”；向右拖动时 ， 行号不变；向下拖动时，行号会以步长 1 递增
- $D$2:$D$10：辅助列区域，即班级名是第几次出现；比如“一班”一共出现了 3 次，那么 row 函数最多匹配到 row(A3)，再下拉到 row(A4) 的时候，就查找不到“4”了，用此方式可以查找出所有“一班”依次出现在搜索区域的第几行
- 最后在外面套上 iferror 函数，不显示错误值
现在 ， 筛选下拉菜单，就能查找出班级中所有人的完整信息了 。为了美观起见，把辅助列 D 的字体颜色设置为白色 。
很多同学会觉得 Excel 单个案例讲解有些碎片化，初学者未必能完全理解和掌握 。不少同学都希望有一套完整的图文教学，从最基础的概念开始，一步步由简入繁、从入门到精通 ， 系统化地讲解 Excel 的各个知识点 。
【Excel – 如何根据班级名，一对多查找出全班人的姓名和成绩？】现在终于有了，以下专栏 ， 从最基础的操作和概念讲起，用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本，你也能成为 Excel 高手 。