如何从SHEET1提取SHEET2指定学生的多记录数据至SHEET3？

问题背景 #

现有三个工作表：

• SHEET1：存储所有学生的多条成绩记录（部分学生存在多个条目）
• SHEET2：列出需要筛选的学生名单
• SHEET3：需生成仅包含SHEET2所列学生的全部SHEET1记录

原始数据示例 #

SHEET1数据

STUDENT CLASS   INSTRUCTOR  SCORE
JOHN    A       1           49
JOHN    A       2           15
JOHN    B       1           94
MARY    A       1           23
MARY    B       2           32
KATE    C       3           76
KATE    D       1           73
KATE    D       1           56
KATE    D       2           7
KATE    C       7           71
JOE     C       5           92
JOE     D       1           94

SHEET2数据

STUDENT
MARY
JOE

目标输出（SHEET3）

STUDENT CLASS   INSTRUCTOR  SCORE
MARY    A       1           23
MARY    B       2           32
JOE     C       5           92
JOE     D       1           94

VLOOKUP失败原因：VLOOKUP默认仅返回第一个匹配到的记录，无法提取同一学生的多条数据，因此不适用该场景。

解决方案 #

方法1：高级筛选（适配所有Excel版本） #

1. 打开SHEET1，选中包含表头的完整数据区域
2. 点击菜单栏「数据」→「高级」
3. 在弹出对话框中设置：
   • 选择「将筛选结果复制到其他位置」
   • 「列表区域」选择SHEET1的全部数据（例如$A$1:$D$13）
   • 「条件区域」选择SHEET2的学生名单（包含表头，例如$SHEET2$A$1:$A$3）
   • 「复制到」选择SHEET3的A1单元格
4. 点击「确定」，即可生成目标数据

方法2：动态数组函数FILTER（Excel 365/2021及以上版本） #

在SHEET3的A1单元格手动输入表头，然后在A2单元格输入以下公式，按下回车后会自动溢出填充所有符合条件的记录：

=FILTER(SHEET1!A:D, ISNUMBER(XMATCH(SHEET1!A:A, SHEET2!A:A)))

方法3：INDEX+SMALL+IF组合公式（旧版无动态数组Excel） #

在SHEET3的A2单元格输入以下数组公式，输入完成后按Ctrl+Shift+Enter确认：

=INDEX(SHEET1!$A:$D, SMALL(IF(ISNUMBER(MATCH(SHEET1!$A:$A, SHEET2!$A:$A, 0)), ROW(SHEET1!$A:$A)), ROW(A1)), COLUMN(A1))

将公式向右拖动至D列，再向下拖动直到出现#NUM!（表示无更多匹配记录），最后删除错误行即可。

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