Excel跨工作表关联匹配：基于部分文本匹配ID与索引的公式方案

一、Sheet2的H列匹配Sheet1的ID（核心需求） #

从Sheet2的H2单元格开始输入以下公式：

新版Excel（支持动态数组，如365/2021） #

=XLOOKUP(TRUE, ISNUMBER(SEARCH(Sheet1!$B$2:$B$15, TEXTJOIN(" ", TRUE, Sheet2!$A2:$F2))), Sheet1!$A$2:$A$15, "无匹配")

下拉填充即可。

旧版Excel（需数组输入） #

=INDEX(Sheet1!$A$2:$A$15, MATCH(TRUE, ISNUMBER(SEARCH(Sheet1!$B$2:$B$15, TEXTJOIN(" ", TRUE, Sheet2!$A2:$F2))), 0))

输入后按Ctrl+Shift+Enter完成数组公式录入，再下拉填充。

原理说明：

• TEXTJOIN(" ", TRUE, Sheet2!$A2:$F2)：把当前行A-F的非空白内容合并成一个字符串，避免空白单元格干扰搜索。
• SEARCH(Sheet1!$B$2:$B$15, ...)：检查Sheet1的每个名称是否包含在合并后的字符串中，返回匹配位置或错误值。
• ISNUMBER(...)：将搜索结果转为布尔值（匹配则为TRUE，不匹配为FALSE）。
• 新版用XLOOKUP直接定位第一个TRUE对应的Sheet1 ID；旧版用MATCH找TRUE的位置，再用INDEX提取对应ID。

二、Sheet1的C列匹配Sheet2的索引 #

从Sheet1的C2单元格开始输入公式：

新版Excel #

=XLOOKUP(TRUE, ISNUMBER(SEARCH(Sheet1!B2, TEXTJOIN(" ", TRUE, Sheet2!$A$2:$F$1000))), Sheet2!$G$2:$G$1000, "无匹配")

注意：把$A$2:$F$1000和$G$2:$G$1000替换成Sheet2实际的数据行范围，避免全列引用拖慢计算。

旧版Excel #

=INDEX(Sheet2!$G$2:$G$1000, MATCH(TRUE, ISNUMBER(SEARCH(Sheet1!B2, TEXTJOIN(" ", TRUE, Sheet2!$A$2:$F$1000))), 0))

同样按Ctrl+Shift+Enter录入数组公式后下拉。

注意事项 #

• 确保Sheet1的B列名称唯一，避免出现多个匹配结果导致公式返回第一个匹配项（若业务允许多匹配，需调整逻辑）。
• 若Sheet2存在同一行包含多个Sheet1名称的情况，公式会优先返回Sheet1中靠前的名称对应的ID/索引，需确认业务需求。

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