如何用公式比对数据集两列相似词汇并生成最大匹配内容？

针对Office 365/Excel 2021（支持动态数组） #

假设待比对内容分别在A列和B列，在C2单元格输入以下公式，下拉即可批量应用：

=LET(
    aWords, TEXTSPLIT(A2, " ", , TRUE),
    bWords, TEXTSPLIT(B2, " ", , TRUE),
    matches, FILTER(aWords, ISNUMBER(XMATCH(aWords, bWords))),
    longest, INDEX(matches, MODE(MATCH(LEN(matches), LEN(matches), 0))),
    IFERROR(longest, "")
)

公式逻辑：

• TEXTSPLIT 将单元格内容按空格拆分为独立词汇数组，最后一个参数TRUE用于忽略空值
• FILTER + XMATCH 筛选出两列共有的词汇
• LEN + MODE 找出匹配词汇中长度最长的项（若存在多个同长度最长匹配，返回第一个出现的）
• IFERROR 处理无匹配项的情况，返回空字符串

针对旧版Excel（不支持动态数组） #

在C2单元格输入以下数组公式，输入完成后按 Ctrl+Shift+Enter 确认，再下拉批量应用：

=IFERROR(INDEX(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))*99-98,99)),MODE(IF(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))*99-98,99)),TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1))*99-98,99)),0)),LEN(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))*99-98,99))))),"")

公式逻辑：

• 通过SUBSTITUTE+MID+ROW模拟拆分单元格词汇的效果（旧版Excel无TEXTSPLIT函数）
• MATCH 比对两列词汇，筛选出共有项
• LEN+MODE 定位最长匹配词汇，INDEX返回对应结果
• IFERROR 处理无匹配场景，返回空值

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