Excel公式未匹配到值时返回0而非"Not found"的问题排查与解决

需求说明 #

• 对比D列和R列所有值
• 找出R列存在但D列不存在的值
• 仅考虑D列对应行A列值等于DRM!$W$1的情况
• 忽略1-5行
• 从AR6单元格开始列出符合条件的R列值

原公式 #

=IF(ISERROR(INDEX($R$6:$R$1000,SMALL(IF(ISERROR(MATCH($R$6:$R$1000,$D$6:$D$1000,0))),IF($A$6:$A$1000=DRM!$W$1,ROW($R$6:$R$1000)-ROW($R$6)+1)),ROW()-ROW($AS$5))),"Not found",INDEX($R$6:$R$1000,SMALL(IF(ISERROR(MATCH($R$6:$R$1000,$D$6:$D$1000,0))),IF($A$6:$A$1000=DRM!$W$1,ROW($R$6:$R$1000)-ROW($R$6)+1)),ROW()-ROW($AS$5)))

问题现象 #

• 当D列有匹配值时公式正常工作
• 无匹配值时返回0而非预期的"Not found"
• 已尝试数组公式（Ctrl+Shift+Enter）和普通公式模式，针对Z列和K列使用同款公式可正常返回结果

环境信息 #

Windows 10系统，Microsoft® Excel® for Microsoft 365 MSO（版本2212 内部版本16.0.15928.20278）64位版本

相关数据 #

• DRM!$W$1 = IRDC
• 样本数据：

Colum A (...)   Colum D (...)   Colum R
DATREF              
Rel              
                
Lize        Chave BC_P      Chave BC_P
IRDC        JP1_CDI     JP1_CDI
IRDC        JP1_COM     JP1_COM
IRDC        JP1_CRA     JP1_CRA
IRDC        JP1_DEB     JP1_DEB
IRDC        JP1_PRO     JP1_PRO
IRDC        JP1_REN     JP1_REN
IRDC        JP1_REP     JP1_REP
IRDC        JM1_REP     JM1_REP
IRDC        JM1_SWA     JM1_SWA
IRDC        JM2_REC     JM2_REC
IRDC        JM2_SWA     JM2_SWA
IRDC        JI2_OPE     JI2_OPE
IRDC        JI2_PES     JI2_PES
IRDC        JI1_COM     JI1_COM
IRDC        JI1_NTN     JI1_NTN
IRDC        JI1_OPE     JI1_OPE
IRDC        JI1_REC     JI1_REC
IRDC        JI1_REN     JI1_REN
IRDC        JI1_REP     JI1_REP
IRDC        JI1_REP     JI1_REP
IRDC        JJ1_CAP     JJ1_COM
IRDC        JJ1_CAP     JJ1_OPE
IRDC        JJ1_CDB     JJ1_PES
IRDC        JJ1_COM     JJ1_PRO
IRDC        JJ1_OPE     JJ1_REC
IRDC        JJ1_PES     JJ1_REN
IRDC        JJ1_PRO     JJ1_REP
IRDC        JJ1_REC     JJ1_REP
IRDC        JJ1_REN     JJ1_REP
IRDC        JJ1_REP     JJ1_REP
IRDC        JJ1_REP     JJ1_REP
IRDC        JJ1_REP     JJ1_REP
IRDC        JJ1_REP     JP2_COM
IRDC        JJ1_REP     JP2_LFT
IRDC        JJ1_REP     JP2_PRO
IRDC        JJ1_SWA     JP2_REC
IRDC        JP2_COM     JP2_REN
IRDC        JP2_LFT     JP2_REP
IRDC        JP2_PRO     JP2_REP
IRDC        JP2_REC     JP2_REP
IRDC        JP2_REN     JT2_REC
IRDC        JP2_REP     JT2_REN
IRDC        JP2_REP     JT2_REP
IRDC        JP2_REP     JT2_REP
IRDC        JT2_REC     JI3_REN
IRDC        JT2_REN     JI3_REP
IRDC        JT2_REP     JT1_COM
IRDC        JT2_REP     JT1_OPE
IRDC        JI3_REN     JT1_REC
IRDC        JI3_REP     JT1_REN
IRDC        JT1_COM     JT1_REP
IRDC        JT1_OPE     JT1_REP
IRDC        JT1_REC     JT1_REP
IRDC        JT1_REN     JP1_CAP
IRDC        JT1_REP     JP1_CAP
IRDC        JT1_REP     JP1_CDB
IRDC        JT1_REP     JP1_SWA
IRDC        JP1_CAP     JM1_CAP
IRDC        JP1_CAP     JM1_REP
IRDC        JP1_CDB     JM2_CAP
IRDC        JP1_SWA     JI2_PES
IRDC        JM1_CAP     JI1_CDB
IRDC        JM1_REP     JI1_REP
IRDC        JM2_CAP     JI1_REP
IRDC        JI2_PES     JJ1_CAP
IRDC        JI1_CDB     JJ1_CAP
IRDC        JI1_REP     JJ1_CDB
IRDC        JI1_REP     JJ1_REP
IRDC        JJ1_CAP     JJ1_REP
IRDC        JJ1_CAP     JJ1_REP
IRDC        JJ1_CDB     JJ1_REP
IRDC        JJ1_DEB     JJ1_REP
IRDC        JJ1_REP     JJ1_SWA
IRDC        JJ1_REP     JP2_REP
IRDC        JJ1_REP     JP2_REP
IRDC        JJ1_REP     JT2_REP
IRDC        JJ1_REP     JT2_REP
IRDC        JJ1_SWA     JI3_REP
IRDC        JP2_REP     JT1_REP
IRDC        JP2_REP     JT1_REP
IRDC        JT2_REP     
IRDC        JT2_REP     
IRDC        JI3_REP     
IRDC        JT1_REP     
IRDC        JT1_REP     
IRDC Total              
NA      998_DIS     
NA      999_COT     
NA      999_COT     
NA      999_COT     
NA      ME1_DEP     
NA      ME1_REP     
NA      ME1_SWA     
NA      ME2_DEP     
NA      ME2_REC     
NA      ME2_SWA     
NA      AA1_ACO     
NA      JJ1_COM     
NA      ME1_CAP     
NA      ME1_REP     
NA      ME2_CAP     
NA Total                
Grand total            

解决方案 #

原因分析 #

原公式中ISERROR仅捕获INDEX/SMALL的错误，但当SMALL返回的行号指向空单元格时，INDEX会返回0而非错误，导致IF判断失效，无法触发"Not found"。

修复后的公式 #

方案1（适配Excel 365动态数组）

直接使用FILTER函数简化逻辑，无需下拉数组公式：

=IFERROR(FILTER($R$6:$R$1000,ISERROR(MATCH($R$6:$R$1000,$D$6:$D$1000,0))*($A$6:$A$1000=DRM!$W$1)),"Not found")

输入到AR6单元格后，Excel会自动溢出结果，无需下拉。

方案2（兼容旧版/原逻辑修复）

在原公式基础上，增加对INDEX结果为空/0的判断：

=LET(result,INDEX($R$6:$R$1000,SMALL(IF(ISERROR(MATCH($R$6:$R$1000,$D$6:$D$1000,0))*($A$6:$A$1000=DRM!$W$1),ROW($R$6:$R$1000)-ROW($R$6)+1),ROW()-ROW($AS$5))),IF(OR(result="",result=0),"Not found",result))

注：使用LET函数简化重复计算，同时合并两个IF条件为*逻辑（数组中TRUE=1, FALSE=0，相乘即同时满足），最后判断结果是否为空或0，返回对应值。

验证说明 #

• 方案1利用Excel 365的动态数组特性，逻辑更简洁，自动处理溢出和无结果场景
• 方案2保留原公式的数组逻辑，修复了空单元格返回0的问题，需按数组公式输入（Ctrl+Shift+Enter）或在Excel 365中直接回车

内容的提问来源于stack exchange，提问作者Igor Mendonça