Excel多值列模糊匹配：基于对照表批量返回对应值的优化需求

之前用=IFS(ISNUMBER(SEARCH("Hello",[@Column1])),"Welcome",(ISNUMBER(SEARCH("Hi",[@Column1]))),"Welcome",TRUE,"")这种公式，虽然能实现匹配返回，但搜索项和返回值一多，公式会变得又长又乱，改起来特别麻烦。

现在用「搜索项-返回值」对照表来解决，不仅支持不区分大小写的任意位置模糊匹配，后续维护只需要改对照表，不用碰公式，效率高多了。

操作步骤 #

1. 准备对照表
   把所有搜索项和对应的返回值整理成两列，比如放在$F$2:$G$5区域（表头设为“搜索”和“返回”），示例如下：

   搜索	返回
   Cat	Yes
   Human	No
   Dog	Yes
   Baby	No

2. 使用核心公式
   假设待搜索文本在A列，要在B列返回结果，直接在B2单元格输入以下公式，下拉填充即可：

   • 新版Excel（支持XLOOKUP）：

     =XLOOKUP(TRUE,ISNUMBER(SEARCH($F$2:$F$5,A2)),$G$2:$G$5,"",2)
     

   • 旧版Excel（用INDEX+MATCH）：

     =IFERROR(INDEX($G$2:$G$5,MATCH(TRUE,ISNUMBER(SEARCH($F$2:$F$5,A2)),0)),"")
     

公式说明 #

• SEARCH($F$2:$F$5,A2)：不区分大小写，检查A2文本里是否包含对照表中的每个搜索项，匹配到返回位置，没匹配到返回错误值
• ISNUMBER(...)：把上面的结果转换成TRUE/FALSE，定位第一个匹配成功的项
• XLOOKUP/INDEX+MATCH：根据第一个TRUE的位置，返回对照表中对应的“返回值”，没匹配到就返回空字符串

效果验证 #

用示例文本测试，结果完全符合预期：

维护技巧 #

后续要新增搜索规则，直接在对照表末尾加行；要修改规则，直接改对应单元格的内容就行，公式不用动，自动适配所有新规则。

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