Google Sheets实现两数据集近似匹配并返回匹配值的公式咨询

原公式适配问题的核心原因 #

• 原VLOOKUP匹配方向反向：原逻辑是给List1单元格加前后通配符去匹配List2整列，本质是寻找“List2单元格等于List1内容拼接任意前后缀”的场景，和实际需要的“List2单元格作为任意片段存在于List1当前文本中”的匹配逻辑完全相反，是匹配率低的核心原因。
• 原AGGREGATE公式为Excel专属函数，Google Sheets无原生支持，无法正常运行。
• 原QUERY公式的contains参数仅支持硬编码固定值，无法动态跨表批量引用匹配源，不适合双数据集的自动比对场景。

可直接复用的适配公式 #

1. 返回匹配到的List2对应值 #

如果需要在List1中输出当前行命中的所有List2匹配内容，可在List1结果列（比如G列旁的H2单元格）输入以下公式：

• 逐行适配版本（适合万级数据，可直接向下拖动填充）：
  =IF(G2="","",IFERROR(TEXTJOIN("、",TRUE,FILTER(Dropdowns!$K$2:$K$500,ISNUMBER(SEARCH(Dropdowns!$K$2:$K$500,G2))),""))
• 整列自动溢出版本（无需手动拖动，大表场景减少重复计算）：
  =ARRAYFORMULA(IF(G2:G="","",IFERROR(TEXTJOIN("、",TRUE,FILTER(Dropdowns!$K$2:$K$500,ISNUMBER(SEARCH(TRANSPOSE(Dropdowns!$K$2:$K$500),G2:G))),"")))

2. 仅标记命中行（不返回具体匹配值） #

如果仅需要标记List1中和List2存在模糊匹配的行，可使用简化后的批量判断公式：
=ARRAYFORMULA(IF(G2:G="","",IF(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(Dropdowns!$K$2:$K$500),G2:G))),ROW(Dropdowns!$K$2:$K$500)^0)>0,"✅ 命中","")))

优化提示 #

• 可根据实际数据范围，将公式中Dropdowns!$K$2:$K$500修改为实际的非空单元格范围，减少公式遍历空值的计算开销，大幅提升大表运行速度。
• 如需区分大小写的匹配，可将公式中的SEARCH函数替换为FIND函数（SEARCH为不区分大小写匹配，FIND为区分大小写匹配）。

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