求助：Excel INDEX & MATCH多列匹配日程转换及相关问题

一、修正INDEX & MATCH多条件匹配公式（支持拖拽、易增减人员） #

你的现有公式仅完成了单条件的行/列匹配，缺少「人员+星期」的复合列条件，以下是适配的多条件公式（假设左侧数据结构：A列是日期，B1:AJ1是人员名，B2:AJ2是星期几，B4:AJ18是标记的"x"）：

=INDEX($B$4:$AJ$18,
        MATCH($AL4,$A$4:$A$18,0),
        MATCH(LEFT(AM$3,FIND("-",AM$3)-1)&RIGHT(AM$3,LEN(AM$3)-FIND("-",AM$3)),
              $B$1:$AJ$1&$B$2:$AJ$2,
              0))

注：旧版Excel需按Ctrl+Shift+Enter触发数组运算；新版Excel会自动识别数组公式。

关键逻辑说明： #

• 多列匹配核心：将左侧的「人员名」（B1:AJ1）和「星期几」（B2:AJ2）拼接成复合键，与右侧表头的「人员-星期」格式（如"张三-周一"）匹配，实现多条件列定位。
• 拖拽适配：公式中$AL4锁定列、动态取行（日期），AM$3锁定行、动态取列（复合表头），横向/纵向拖拽时自动适配对应日期和人员星期组合。
• 易增减人员：左侧添加/删除人员列后，右侧对应新增/删除「人员-星期」格式的表头即可，公式引用的是整个B:AJ区域，无需修改公式范围（若要更严谨，可把$B$4:$AJ$18改成动态区域，比如OFFSET($A$4,0,1,COUNTA($A$4:$A$100),COUNTA($1:$1)-1)）。

二、更简便的替代方法 #

1. XLOOKUP多条件匹配（推荐，公式更简洁） #

如果你的Excel版本支持XLOOKUP（2021及以后/365），可直接用多条件拼接的方式：

=XLOOKUP($AL4&AM$3,$A$4:$A$18&$B$1:$AJ$1&$B$2:$AJ$2,$B$4:$AJ$18,"无数据")

该公式直接把「日期+人员+星期」作为复合匹配键，一步定位目标单元格，无需拆分INDEX和MATCH。

2. SUMPRODUCT定位法 #

适合不支持数组公式的旧版Excel，用SUMPRODUCT返回符合条件的单元格位置，再嵌套INDEX：

=INDEX($B$4:$AJ$18,
        MATCH($AL4,$A$4:$A$18,0),
        SUMPRODUCT(($B$1:$AJ$1=LEFT(AM$3,FIND("-",AM$3)-1))*($B$2:$AJ$2=RIGHT(AM$3,LEN(AM$3)-FIND("-",AM$3)))*COLUMN($B$1:$AJ$1))-COLUMN($B$1)+1)

逻辑是用SUMPRODUCT计算符合人员和星期条件的列号，再转换为INDEX的列参数。

三、合并单元格的兼容性问题 #

合并单元格的引用规则是仅合并区域的左上角单元格存储实际值，其他单元格引用时会返回左上角的值：

• 若左侧表头用合并单元格（比如人员名跨周一至周五列合并）：公式可正常工作，因为$B$1:$AJ$1中合并区域的所有单元格都会返回人员名，拼接星期几后仍能匹配右侧表头。
• 若数据行/列用合并单元格（比如日期合并星期几的行）：会导致MATCH日期时出错，因为合并行的其他单元格引用时返回的是左上角的日期值，无法精准匹配。
• 双向引用限制：合并单元格无法实现“反向引用每个子单元格”，即无法通过公式单独引用合并区域内的某个子单元格，只能引用整个合并区域的左上角值。因此不建议在数据区域使用合并单元格，可用「跨列居中」格式替代视觉上的合并，不影响公式引用。

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