Excel中如何实现Mentees与Mentors基于项目的跨表匹配

工作表基础结构 #

匹配工作共涉及3个工作表：

• Mentees：学员信息表，存储学员姓名、所属项目等核心字段
• Mentors：导师信息表，存储导师姓名、所属项目等核心字段
• MenteesXMentors：匹配结果输出表，需基于「所属项目」字段生成有效匹配对，同项目的导师与学员即为有效匹配（例：Henry Paul与Josie Sterla所属项目一致，属于有效匹配对）

INDEX+MATCH返回列标题的核心原因 #

公式返回列标题属于典型的引用范围错误：

• MATCH函数的查找范围包含了表头行，当匹配逻辑触发第一行命中时，会直接返回表头的列标题内容
• INDEX函数的行/列偏移量计算错误，返回范围指向了表头所在行

可直接落地的实现方案 #

方案1：Excel 365/2021及以上版本（支持动态数组） #

在MenteesXMentors表的首个数据单元格（表头下方第一行的空白单元格，不要选中表头）输入以下公式，会自动溢出所有符合规则的匹配对：

=FILTER(
    CHOOSE({1,2},Mentees!A2:A1000,Mentors!A2:A1000),
    Mentees!B2:B1000=Mentors!B2:B1000,
    "无有效匹配对"
)

公式中A2:A1000对应姓名列数据范围，B2:B1000对应所属项目列数据范围，可根据表格实际列号、数据总行数调整范围，所有选取范围均从表头下第一行数据开始，禁止包含表头行。

方案2：旧版Excel（无动态数组功能） #

针对不支持FILTER动态溢出的旧版本Excel，按以下步骤操作：

1. 为两个基础表添加辅助列：在Mentees表C2单元格输入同项目学员序号统计公式，下拉填充至所有学员数据行：

   =COUNTIF(B$2:B2,B2)
   

2. 在Mentors表C2单元格输入同项目导师序号统计公式，下拉填充至所有导师数据行：

   =COUNTIF(B$2:B2,B2)
   

3. 回到MenteesXMentors表，A列输出匹配学员、B列输出匹配导师，在A2单元格输入学员匹配公式：

   =IFERROR(INDEX(Mentees!A:A,MATCH(ROW(A1),Mentees!C:C,0)),"")
   

   在B2单元格输入导师匹配公式：

   =IFERROR(INDEX(Mentors!A:A,MATCH(ROW(A1),Mentors!C:C,0)),"")
   

   选中A2、B2单元格同步下拉填充，直到单元格返回空值，即完成所有有效匹配对输出。

结果校验 #

输出完成后随机抽取3-5组匹配对，核对双方所属项目字段完全一致，无跨项目错配即为结果有效。

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