如何修改LOOKUP公式使其在含空单元格的区域正常返回最新Error值

问题原因 #

原公式的判断条件仅为<> "O.K."，空单元格也符合该判定规则，因此计算1/(区域<>"O.K.")时，B7、B8对应的空单元格会生成有效值1。LOOKUP查找2时会匹配到最后一个符合条件的空单元格，Excel默认将空单元格取值为0，就是你得到的返回结果。

修正后的公式 #

只需在判断条件中额外排除空单元格即可，修改后的公式如下（以B9单元格为例，可直接横向拖拽适配其他列）：

=LOOKUP(2;1/((B1:B8<>"O.K.")*(B1:B8<>""));B1:B8)

如果你的Excel版本支持XLOOKUP，也可以用更易读的写法，还支持自定义无匹配场景的返回值：

=XLOOKUP(TRUE;B1:B8<>"O.K.";B1:B8;"无匹配";0;-1)

补充说明 #

如果需要在未找到任何Error值的场景下返回空而非错误值，可在外层嵌套IFERROR：

=IFERROR(LOOKUP(2;1/((B1:B8<>"O.K.")*(B1:B8<>""));B1:B8);"")

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