Office 365中XLOOKUP用通配符查找返回#value!错误的解决方法

问题原因 #

A列字符串包含Excel通配符特殊字符（*、?、~）时，直接用"*"&E1&"*"作为查找值会让Excel把这些特殊字符当成通配符规则解析，而非普通文本，导致匹配逻辑失效，返回#VALUE!。

解决方法 #

方法1：转义通配符特殊字符 #

通过嵌套SUBSTITUTE函数，将查找子字符串中的特殊字符逐一转义（Excel用~作为通配符转义符），再用XLOOKUP匹配：

=XLOOKUP("*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E1,"~","~~"),"*","~*"),"?","~?")&"*",A:A,B:B,,2)

• 转义逻辑：先转义~（需写成~~，因为~本身是转义符），再转义*和?，确保这些字符被当作普通文本匹配。

方法2：用SEARCH+XLOOKUP组合匹配 #

利用SEARCH（不区分大小写）或FIND（区分大小写）函数判断子字符串是否存在，再通过XLOOKUP返回对应值，无需手动转义：

=XLOOKUP(TRUE,ISNUMBER(SEARCH(E1,A:A)),B:B,,0)

• 原理：SEARCH(E1,A:A)会返回子字符串在A列单元格中的起始位置，找不到则返回错误值；ISNUMBER将结果转为布尔值，XLOOKUP匹配第一个TRUE对应的B列数值。

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