如何用Excel公式基于单元格内措辞跨表获取最佳匹配案例编号

核心思路 #

把Table 01目标单元格里的词汇拆分后，统计Table 02每个案例对应的匹配词汇数量，最终返回匹配数最多的案例编号；若多个案例匹配数相同，返回最先出现的编号。

公式实现（Excel 365/2021适用） #

假设：

• Table 02的案例编号列为Table02[案例编号]，关键词列为Table02[关键词]
• 在Table 01的结果列（如E2单元格）输入以下数组公式：

=INDEX(Table02[案例编号],MATCH(MAX(BYROW(Table02[关键词],LAMBDA(x,SUMPRODUCT(--ISNUMBER(SEARCH(TEXTSPLIT(D2," ",,1),x)))))),BYROW(Table02[关键词],LAMBDA(x,SUMPRODUCT(--ISNUMBER(SEARCH(TEXTSPLIT(D2," ",,1),x))))),0))

公式拆解 #

• TEXTSPLIT(D2," ",,1)：按空格拆分Table 01单元格D2的词汇，自动忽略空值
• SEARCH(...,x)：检查拆分后的每个词汇是否出现在Table 02的关键词单元格x中，返回匹配位置或错误值
• --ISNUMBER(...)：把匹配位置转为1，错误值转为0，方便统计
• SUMPRODUCT(...)：对单个案例的匹配词汇总数求和
• BYROW(...,LAMBDA(x,...))：遍历Table 02所有关键词行，计算每个案例的匹配数
• MAX(...)：找出所有案例里的最大匹配数
• INDEX+MATCH：根据最大匹配数定位对应的案例编号

兼容性处理（旧版Excel） #

如果无法使用LAMBDA/BYROW函数，可使用以下数组公式（输入后按Ctrl+Shift+Enter确认）：

=INDEX(Table02[案例编号],MATCH(MAX(SUMPRODUCT(--ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(D2," ",REPT(" ",99)),(ROW($1:$99)-1)*99+1,99)),Table02[关键词])))),SUMPRODUCT(--ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(D2," ",REPT(" ",99)),(ROW($1:$99)-1)*99+1,99)),Table02[关键词]))),0))

注意事项 #

• 确保词汇分隔符统一：示例用空格作为分隔符，若实际是逗号、顿号等，需替换公式里的分隔符参数
• 若需要精确匹配（而非包含匹配），把公式里的SEARCH替换为EXACT，同时保证拆分后的词汇与目标关键词完全一致

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