Excel中INDEX与AGGREGATE公式报错及去重问题咨询

一、原公式问题分析 #

原公式：

=IFERROR(INDEX($G$168:$G$196,AGGREGATE(15,6,(ROW($G$168:$G$196)-ROW($G$168)+1)/(($G28=$H$168:$H$196)*($D$28=$F$168:$F$196)),COUNTIFS($H$168:$H$196,$G28,$F$168:$F$196,$D$28))),"")

核心问题出在AGGREGATE函数的第4参数：使用COUNTIFS(...)返回符合条件的总条数，导致每次调用公式时，都会取最后一个符合条件的匹配项。如果下拉复用公式，就会重复返回同一内容。

二、实现预期结果的优化方案 #

要将其中一条重复的“Reconfirm email address in booking 0”替换为“Reconfirm cancellation policy 0”，需分两步调整：

1. 修正匹配项取值逻辑 #

将AGGREGATE的第4参数改为动态序号（当前行相对于公式起始行的偏移量），确保下拉时依次取第1、第2个匹配项：

• 假设公式起始单元格为A28，下拉时用ROW()-ROW(A28)+1生成1、2、3...的序号，替代原有的COUNTIFS参数。

2. 新增文本替换判断 #

通过IF函数判断当前取的是第几个匹配项，对第二个匹配项执行文本替换：

优化后的完整公式（适用于A28单元格，下拉复用）：

=IFERROR(LET(matched_val,INDEX($G$168:$G$196,AGGREGATE(15,6,(ROW($G$168:$G$196)-ROW($G$168)+1)/(($G28=$H$168:$H$196)*($D$28=$F$168:$F$196)),ROW()-ROW(A28)+1)),IF(ROW()-ROW(A28)+1=2,SUBSTITUTE(matched_val,"Reconfirm email address in booking","Reconfirm cancellation policy"),matched_val)),"")

兼容旧版本Excel的替代方案 #

若使用Excel 2019及更早版本（不支持LET函数），可拆分公式为：

=IFERROR(IF(ROW()-ROW(A28)+1=2,SUBSTITUTE(INDEX($G$168:$G$196,AGGREGATE(15,6,(ROW($G$168:$G$196)-ROW($G$168)+1)/(($G28=$H$168:$H$196)*($D$28=$F$168:$F$196)),ROW()-ROW(A28)+1)),"Reconfirm email address in booking","Reconfirm cancellation policy"),INDEX($G$168:$G$196,AGGREGATE(15,6,(ROW($G$168:$G$196)-ROW($G$168)+1)/(($G28=$H$168:$H$196)*($D$28=$F$168:$F$196)),ROW()-ROW(A28)+1))),"")

补充提示 #

如果数据源中本应存在两个不同的匹配项（一个是邮箱确认、一个是取消政策），请先检查$G28=$H$168:$H$196和$D$28=$F$168:$F$196这两个匹配条件是否有误，是否错误地将两个不同的项归为同一匹配组。

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