使用VLOOKUP或FILTER跨工作簿返回多匹配结果的问题求助

问题原因拆解 #

• VLOOKUP仅返回第一个匹配项，因此多条目场景下无法获取全部对应数据库编号。
• FILTER公式返回#CALC错误，是因为直接用=结合通配符的写法不生效——Excel的等式判断不支持通配符，需用模糊匹配函数构建筛选条件。

解决方案（Excel 365/2021 动态数组版本） #

使用FILTER结合ISNUMBER(SEARCH(...))实现模糊匹配，再通过TEXTJOIN将多个结果合并到同一单元格：

=TEXTJOIN(", ", TRUE, FILTER([workbook 2.xlsx]worksheet A!$D:$D, ISNUMBER(SEARCH(A3, [workbook 2.xlsx]worksheet A!$A:$A))))

• 细节说明：
  • SEARCH(A3, 目标列)：检查A3内容是否包含在工作簿2的A列中，天然支持模糊匹配，无需手动添加*
  • ISNUMBER(...)：将SEARCH的结果转换为布尔值（找到返回TRUE，未找到返回FALSE），作为FILTER的筛选条件
  • TEXTJOIN：用指定分隔符（此处为, ）合并所有匹配的数据库编号，第二个参数TRUE用于忽略空值

若需精确匹配（工作簿2的A列内容与A3完全一致），将SEARCH替换为EXACT即可：

=TEXTJOIN(", ", TRUE, FILTER([workbook 2.xlsx]worksheet A!$D:$D, EXACT([workbook 2.xlsx]worksheet A!$A:$A, A3))))

旧版Excel（无动态数组）解决方案 #

如果你的Excel版本不支持FILTER和TEXTJOIN，可采用数组公式结合INDEX+SMALL逐个提取结果：

1. 提取第一个匹配项（工作簿1的B3单元格）：

=IFERROR(INDEX([workbook 2.xlsx]worksheet A!$D:$D, SMALL(IF(ISNUMBER(SEARCH(A3, [workbook 2.xlsx]worksheet A!$A:$A)), ROW([workbook 2.xlsx]worksheet A!$A:$A)-ROW([workbook 2.xlsx]worksheet A!$A$1)+1), 1)), "")

输入完成后按Ctrl+Shift+Enter触发数组公式（旧版Excel强制要求）
2. 提取第二个匹配项时，将公式末尾的1改为2，以此类推：

=IFERROR(INDEX([workbook 2.xlsx]worksheet A!$D:$D, SMALL(IF(ISNUMBER(SEARCH(A3, [workbook 2.xlsx]worksheet A!$A:$A)), ROW([workbook 2.xlsx]worksheet A!$A:$A)-ROW([workbook 2.xlsx]worksheet A!$A$1)+1), 2)), "")

额外注意事项 #

• 确保两个工作簿处于打开状态，否则跨工作簿引用可能失效
• 若工作簿2数据量较大，避免使用整列引用（如$A:$A），改为实际数据范围（如$A$2:$A$1000），提升公式运行效率
• 如需区分大小写的匹配，将SEARCH替换为FIND

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