Excel中实现参考表子串匹配填充对应值的方法求助

嘿，我明白你的困扰了！普通的VLOOKUP或INDEX(MATCH...)之所以没成功，是因为它们默认是匹配单元格完整内容，而你需要的是从Sheet1的关键词列表里，找到第一个出现在Sheet2 D列文本中的子串，再返回对应B列的值。下面给你两种实用的解决方案：

方案1：适用于Excel 365/2021（支持动态数组） #

在Sheet2的E2单元格输入以下公式，然后下拉填充即可：

=XLOOKUP(TRUE,ISNUMBER(SEARCH(Sheet1!$A$2:$A$3,D2)),Sheet1!$B$2:$B$3,"无匹配")

公式解释： #

• SEARCH(Sheet1!$A$2:$A$3,D2)：逐个检查Sheet1 A列的关键词是否在D2文本中，找到返回位置，找不到返回错误值
• ISNUMBER(...)：把上面的结果转换成布尔值（找到为TRUE，找不到为FALSE）
• XLOOKUP：查找第一个TRUE对应的Sheet1 B列的值，最后一个参数是无匹配时显示的内容（可自定义）

方案2：适用于旧版Excel（不支持动态数组） #

在Sheet2的E2单元格输入以下数组公式，按Ctrl+Shift+Enter确认（部分新版Excel直接回车也生效），再下拉填充：

=IFERROR(INDEX(Sheet1!$B$2:$B$3,MATCH(TRUE,ISNUMBER(SEARCH(Sheet1!$A$2:$A$3,D2)),0)),"无匹配")

公式解释： #

• 核心逻辑和方案1一致，用INDEX(MATCH...)组合替代XLOOKUP实现查找第一个匹配项
• IFERROR用来处理无匹配的情况，避免显示错误值

关键注意事项 #

• 关键词优先级：公式会返回Sheet1 A列中第一个匹配的关键词对应的值，如果你希望某些关键词优先匹配，调整Sheet1 A列的顺序即可
• 大小写敏感：上面的公式用SEARCH是不区分大小写的，如果需要区分大小写，把SEARCH换成FIND
• 范围调整：如果Sheet1的关键词不止2行，把公式里的$A$2:$A$3和$B$2:$B$3改成实际的范围（比如$A$2:$A$100）

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