Excel嵌套IF+INDEX+MATCH函数出现值错误问题求助

问题背景 #

需求：在Input_Jira工作表的B列中找到包含G90内容的行，检查该行的第2列（B列）和第13列（N列）是否包含{"neg", "negativ", "negative", "negatives", "Bonität"}中的任一字符串，只要有一列满足就返回"rejected"。

原单列公式可正常运行：

=if(SEARCH({"neg", "negativ", "negative", "negatives", "Bonität"},index(Input_Jira!$B$1:$N$2000,MATCH("*"&G90&"*",Input_Jira!B:B,0),2)),"rejected","")

但扩展为两列搜索的嵌套公式后出现#VALUE!错误：

=if(SEARCH({"neg", "negativ", "negative", "negatives", "Bonität"}, index(Input_Jira!$B$1:$N$2000,MATCH("*"&G90&"*",Input_Jira!B:B,0),13)),"rejected",if(SEARCH({"neg", "negativ", "negative", "negatives", "Bonität"},index(Input_Jira!$B$1:$N$2000,MATCH("*"&G90&"*",Input_Jira!B:B,0),2)),"rejected",""))

错误原因 #

SEARCH函数在找不到匹配字符串时会直接返回#VALUE!错误，而IF函数无法处理这种错误值。当嵌套公式中某一列没有匹配到任何关键词时，对应的SEARCH就会抛出错误，导致整个公式失效。

解决方案 #

用ISNUMBER函数包裹SEARCH，将搜索结果转换为布尔值（找到匹配返回TRUE，找不到返回FALSE），再用OR判断两列是否有任一满足条件，最后用IF返回结果。同时可以把重复的计算部分提取出来，提升效率和可读性。

支持LET函数的Excel版本（推荐） #

=LET(
    match_row, MATCH("*"&G90&"*", Input_Jira!B:B, 0),
    col2_val, INDEX(Input_Jira!$B:$B, match_row),
    col13_val, INDEX(Input_Jira!$N:$N, match_row),
    keywords, {"neg", "negativ", "negative", "negatives", "Bonität"},
    IF(OR(ISNUMBER(SEARCH(keywords, col2_val)), ISNUMBER(SEARCH(keywords, col13_val))), "rejected", "")
)

公式说明

• match_row：定位B列中包含G90内容的行号，避免重复计算
• col2_val/col13_val：提取目标行对应列的单元格值
• keywords：统一存储关键词数组，方便后续修改维护
• ISNUMBER(SEARCH(...))：将搜索结果转为布尔值，消除找不到匹配时的错误
• OR(...)：判断两列是否有任一匹配成功
• IF：根据判断结果返回"rejected"或空值

兼容旧版Excel的公式 #

如果你的Excel版本不支持LET函数，可使用以下版本：

=IF(OR(ISNUMBER(SEARCH({"neg", "negativ", "negative", "negatives", "Bonität"}, INDEX(Input_Jira!$B:$B, MATCH("*"&G90&"*", Input_Jira!B:B, 0)))), ISNUMBER(SEARCH({"neg", "negativ", "negative", "negatives", "Bonität"}, INDEX(Input_Jira!$N:$N, MATCH("*"&G90&"*", Input_Jira!B:B, 0))))), "rejected", "")

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