Excel中如何让XLOOKUP查找匹配所有对应记录而非首个结果

问题根源 #

原有公式的XLOOKUP仅做单ID匹配，默认返回同ID下第一行的课程信息做比对，无法遍历同助教的所有过往任职记录，导致靠后位置的匹配课程被漏判。

方案1：保留XLOOKUP实现全量自动匹配（优先推荐） #

无需调整表格结构，直接修改判定公式即可，逻辑为同时匹配「助教ID+授课课程」双条件，只要任意一条往届记录满足双条件就判定为匹配，不受同ID记录排序影响。
替换原有公式为：

=IF(ISNA(XLOOKUP(1,('The Pit'!$L:$L=$B5)*('The Pit'!$O:$O=$R5),'The Pit'!$O:$O)),"Job Change/Raises","Same Job")

公式逻辑说明：

• 查找值设为1，查找数组为两个判断条件的乘积：只有当往届表的助教ID等于当前行B5的ID、且往届课程等于当前行R5的分配课程时，乘积结果才为1，和查找值匹配
• 外层ISNA用于判断XLOOKUP是否找到匹配结果：找不到匹配（即该助教无对应课程的过往任职记录）时返回"Job Change/Raises"，找到匹配则返回"Same Job"

方案2：支持下拉选择匹配记录的手动校验方案 #

如果需要人工选择对应过往记录做校验，可搭配数据验证实现下拉选择功能：

• 在input表找一列空白列作为下拉选择列（以S列为例），在S5单元格输入公式，自动提取当前助教的所有过往授课课程：

  =FILTER('The Pit'!$O:$O,'The Pit'!$L:$L=$B5,"无过往任职记录")
  

• 选中S列所有需要填写的行，打开「数据验证」面板，允许类型选择「序列」，来源填写=S5#引用公式溢出的所有课程选项，此时每行的下拉菜单只会展示对应助教本人的过往课程
• 原判定列公式修改为：

  =IF(R5=S5,"Same Job","Job Change/Raises")
  

  选择下拉选项后即可自动输出判定结果。

备选精简方案（非XLOOKUP） #

如果不强制要求使用XLOOKUP，用COUNTIFS实现逻辑更简洁、大表计算效率更高：

=IF(COUNTIFS('The Pit'!$L:$L,$B5,'The Pit'!$O:$O,$R5)>0,"Same Job","Job Change/Raises")

逻辑为直接统计往届表中同时匹配当前助教ID、当前分配课程的记录数量，数量大于0即判定为匹配。

优化提示：如果往届表数据量较大，建议将公式中整列引用（如'The Pit'!$L:$L）替换为实际数据范围（如'The Pit'!$L$2:$L$2000），可大幅降低计算卡顿概率。

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