Excel表格间动态关联：自动匹配任务执行者与执行日期

先明确表结构（示例） #

假设你有三个表：

• 负责人表（比如Sheet1）：
  • A列：任务名称（可对应多行日期）
  • B列：任务负责人
• 执行日期表（比如Sheet2）：
  • A列：任务名称（和负责人表完全对应）
  • B列：任务执行日期（一个任务可对应多行日期）
• 最终表（比如Sheet3）：
  • A列：待匹配的任务列表
  • 第1行：待匹配的日期列表
  • 交叉单元格：返回对应任务+日期的负责人，无匹配则为空

通用公式（支持所有Excel版本） #

在最终表的B2单元格（对应A2任务、B1日期）输入以下公式，然后向右向下填充：

=IFERROR(INDEX(Sheet1!$B$2:$B$100, MATCH(1, ($A2=Sheet1!$A$2:$A$100)*(B$1=Sheet2!$B$2:$B$100)*(Sheet1!$A$2:$A$100=Sheet2!$A$2:$A$100), 0)), "")

注：旧版Excel需按 Ctrl+Shift+Enter 作为数组公式输入；365/2021版本直接回车即可。

公式逻辑 #

1. ($A2=Sheet1!$A$2:$A$100)：匹配当前行任务与负责人表的任务
2. (B$1=Sheet2!$B$2:$B$100)：匹配当前列日期与执行日期表的日期
3. (Sheet1!$A$2:$A$100=Sheet2!$A$2:$A$100)：关联负责人表和日期表的任务对应关系
4. MATCH(1, ..., 0)：找到同时满足三个条件的行号
5. INDEX(...)：根据行号取出对应负责人，IFERROR处理无匹配的情况返回空

简化版（Excel 365/2021专属） #

利用动态数组和XLOOKUP，输入一次自动填充整个区域：
在最终表的B2单元格输入：

=LET(
    task_list, $A$2:$A$100,
    date_list, $B$1:$Z$1,
    owner_list, XLOOKUP(task_list, Sheet1!$A$2:$A$100, Sheet1!$B$2:$B$100, ""),
    task_date_match, MMULT(--(TRANSPOSE(task_list)=Sheet2!$A$2:$A$100), --(date_list=Sheet2!$B$2:$B$100)),
    IF(task_date_match>0, owner_list, "")
)

公式逻辑 #

• LET定义变量简化公式，避免重复引用
• owner_list一次性获取所有任务对应的负责人
• task_date_match生成任务与日期的匹配矩阵
• 最后根据匹配结果返回负责人或空值

注意事项 #

• 确保所有表中的任务名称完全一致（无多余空格、大小写统一）
• 如果任务-日期组合唯一，公式会精准返回对应负责人；若有重复组合，会返回第一个匹配的结果
• 可将负责人表和日期表转为Excel结构化表格（选中区域按Ctrl+T），引用时会自动扩展范围，无需手动调整单元格区间

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