咨询Excel动态提取两单元格多组匹配值的实现方法

场景说明 #

A列单元格A1值为 AB111 CD000 EF222 GH333 LM555 RS444，B列单元格B1值为 AB111 OP888 GH333 RS444 ZD999，需提取两单元格中的所有共同匹配值并输出到C1，且公式需支持上万行批量处理。

方案1：Excel 365/2021（推荐，动态数组支持） #

使用以下公式，直接输入到C1后下拉即可批量处理：

=TEXTJOIN(" ",TRUE,FILTER(TEXTSPLIT(A1," "),ISNUMBER(XMATCH(TEXTSPLIT(A1," "),TEXTSPLIT(B1," ")))))

公式逻辑：

• TEXTSPLIT(A1," ")：将A1内容按空格拆分为独立元素数组
• XMATCH(TEXTSPLIT(A1," "),TEXTSPLIT(B1," "))：匹配A1拆分后的每个元素在B1拆分数组中的位置，存在匹配则返回位置值，否则返回错误
• ISNUMBER(...)：将匹配结果转换为布尔值（存在匹配为TRUE）
• FILTER(...)：筛选出A1中存在于B1的元素
• TEXTJOIN(" ",TRUE,...)：将筛选结果用空格拼接为最终文本

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

需按 Ctrl+Shift+Enter 输入数组公式，输入后下拉批量处理：

=TEXTJOIN(" ",TRUE,IF(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),ROW($1:$100)*99-98,99)),TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",99)),ROW($1:$100)*99-98,99)),0)),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),ROW($1:$100)*99-98,99)),""))

公式逻辑：

• SUBSTITUTE(A1," ",REPT(" ",99))：将单元格内的单个空格替换为99个连续空格，便于固定长度截取拆分
• MID(...,ROW($1:$100)*99-98,99)：按固定长度截取字符串，模拟拆分出每个独立元素（ROW($1:$100)假设每个单元格最多包含100个元素，可按需调整）
• TRIM(...)：去除截取后元素的多余空格，得到干净的内容
• MATCH(...)：检查A1拆分元素是否存在于B1拆分元素中
• IF(...)：保留匹配到的元素，未匹配则返回空文本
• TEXTJOIN(" ",TRUE,...)：拼接所有匹配元素为最终文本

注意事项 #

• 若单元格内存在多个连续空格，两种方案均会自动处理，无需额外清理
• 上万行数据场景下，Excel 365的动态数组公式运行效率远高于旧版数组公式，优先推荐使用365版本
• 测试时可先在少量行验证公式效果，再批量应用到全表

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