Excel 2010 INDEX/MATCH连续匹配报错问题求助

需求回顾 #

根据第3行（$P$3:$BW$3）的日期，匹配对应列中标记为“TV”的单元格，返回A列（$A$4:$A$37）对应的姓名，实现选定日期后自动筛选当日带“TV”编码的人员。

错误公式分析 #

1. 报错的多层INDEX/MATCH公式：

INDEX(INDEX($A$4:$A$37;0;MATCH(B39;$P$3:$BW$3;0));MATCH($F$42;INDEX(P:BW;0;MATCH(B39;$P$3:$BW$3;0)-1);0)) 

出现#REF!的原因是：INDEX($A$4:$A$37;0;...)中，A列为单列区域，第三个参数（列索引）超出了A列的范围（仅1列），导致引用无效。

2. 旧版本公式逻辑错误：

=INDEX($A$4:$A$37;MATCH(B39;$P$3:$BW$3;0);MATCH($F$42;P4:P37;0))

INDEX函数的参数顺序为(区域, 行号, 列号)，但这里把日期匹配得到的列号放到了行号参数位置，逻辑完全颠倒，因此无法识别“TV”标签。

3. 列关联型公式效率低：

=INDEX('2024'!$A$4:$A$37;MATCH("TV";'2024'!X$4:X$37;0))

需要手动指定列（如X列），无法自动匹配日期对应的列，批量更新时效率极低。

解决方案公式 #

1. 单个“TV”人员的匹配公式（数组公式） #

如果当日只有1个带“TV”标记的人员，使用以下公式，输入后按Ctrl+Shift+Enter确认（Excel 2010需手动触发数组运算）：

=INDEX($A$4:$A$37,MATCH("TV",INDEX($P$4:$BW$37,0,MATCH(B39,$P$3:$BW$3,0)),0))

公式逻辑：

• MATCH(B39,$P$3:$BW$3,0)：定位目标日期在第3行的列位置
• INDEX($P$4:$BW$37,0,上述结果)：提取该日期对应的整列数据
• MATCH("TV",上述提取列,0)：找到该列中“TV”所在的行号（相对于A4:A37的偏移）
• INDEX($A$4:$A$37,上述行号)：返回对应行的姓名

2. 多个“TV”人员的批量匹配公式（数组公式） #

如果当日可能有多个带“TV”标记的人员，使用以下公式，输入后按Ctrl+Shift+Enter确认，下拉公式可依次返回所有匹配姓名：

=IFERROR(INDEX($A$4:$A$37,SMALL(IF(INDEX($P$4:$BW$37,0,MATCH(B39,$P$3:$BW$3,0))="TV",ROW($A$4:$A$37)-ROW($A$3),""),ROW(A1))),"")

公式逻辑：

• IF(INDEX(...)="TV",ROW(...)-ROW($A$3),"")：筛选出目标日期列中值为“TV”的行号（转换为相对于A4:A37的偏移量）
• SMALL(...,ROW(A1))：依次提取第1、第2...个符合条件的行号
• INDEX(...)：返回对应姓名，IFERROR处理无匹配时的空值

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