Excel如何用公式返回单元格包含的查找表匹配子串值

已知数据规则 #

• Sheet1 含raw-info列，存储文本内容，示例值：james brown england、tommy australia 1234、saka ireland、denmark martin
• Sheet2 含countries列，存储待匹配的国家名列表，示例值：england、nigeria、usa、denmark、australia
• 目标：在Sheet1新增new-col列，若raw-info单元格包含countries列中任意值作为子串，返回对应匹配的国家名，无匹配则留空。

方案1：Excel 365/2021及以上版本（支持动态数组） #

在new-col列首个数据单元格（例：raw-info在A列、表头占第1行时，选B2单元格）输入以下公式，按回车后会自动向下填充所有行结果：

=XLOOKUP(TRUE,ISNUMBER(SEARCH(Sheet2!$A$2:$A$6,A2)),Sheet2!$A$2:$A$6,"")

公式逻辑：

• SEARCH(Sheet2!$A$2:$A$6,A2)：逐个遍历国家列表，判断每个国家名是否存在于当前行的raw-info文本中，匹配到则返回子串起始位置，未匹配返回错误值
• ISNUMBER()：将位置结果转换为布尔值，匹配成功返回TRUE，匹配失败返回FALSE
• XLOOKUP定位到第一个TRUE对应的国家名，无匹配则返回空值

提示：SEARCH默认不区分字母大小写，若需严格区分大小写，将公式中SEARCH替换为FIND即可。

方案2：Excel 2019及更早版本（无动态数组支持） #

在B2单元格输入以下公式，输入完成后按Ctrl+Shift+Enter三键确认数组公式，再下拉填充整列即可：

=IFERROR(INDEX(Sheet2!$A$2:$A$6,MATCH(TRUE,ISNUMBER(SEARCH(Sheet2!$A$2:$A$6,A2)),0)),"")

外层嵌套IFERROR是为了无匹配项时直接返回空值，避免显示#N/A错误。

结果验证 #

用示例数据计算后返回结果完全符合预期：

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