为何XLOOKUP公式无法配合隐式通配符使用？附COUNTIF对比示例

可正常工作的公式逻辑 #

公式：=XLOOKUP(1,1/COUNTIF(D2:Dn,"*"&A1&"*"),B1:Bn)（假设D2:Dn与B1:Bn为对应数据范围）

• COUNTIF默认支持通配符：当条件包含*时，会自动识别为模糊匹配（*代表任意长度字符）。COUNTIF(D2:Dn,"*"&A1&"*")会生成一个数组，其中D列单元格包含A1值的位置返回1，不包含的返回0。
• 1/COUNTIF(...)将数组中的1保留为1，0转换为#DIV/0!错误值。
• XLOOKUP的作用是在这个由1和错误值组成的数组中查找1的位置，最终返回B列对应位置的值，实现"包含指定内容"的模糊匹配。

无法正常工作的公式问题所在 #

公式：=XLOOKUP(D1,"*"&A1&"*",B1,,2)
核心错误是参数逻辑颠倒：

• XLOOKUP的参数顺序为XLOOKUP(查找值, 查找范围, 返回范围, [未找到返回值], [匹配模式], [搜索模式])。
• 此处的查找范围被设置为单一文本字符串"*"&A1&"*"（例如A1为"测试"时，字符串为"测试"），而非需要搜索的单元格范围。
• 即便匹配模式设为2（通配符匹配），XLOOKUP也只会检查查找值D1是否等于这个单一的通配符字符串，而非在某个范围中查找包含D1的内容，完全偏离了模糊匹配的需求。

正确的XLOOKUP模糊匹配写法 #

若想用XLOOKUP直接实现"包含指定内容"的模糊匹配，需将通配符放在查找值中，查找范围设为目标列：
=XLOOKUP("*"&A1&"*", D2:Dn, B1:Bn,,2)

• 查找值为带通配符的"*"&A1&"*"，查找范围为D2:Dn，匹配模式设为2启用通配符匹配，即可在D列中找到包含A1值的单元格并返回对应B列内容。

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