寻求ArrayFormula组合实现数据数组与验证集的匹配验证方案

嘿，我来帮你搞定这个字符串匹配的需求！结合你提到的ARRAYFORMULA、VLOOKUP/INDEX/MATCH这类函数，我给你几个实用的方案，直接就能用在表格工具里（Google Sheets、Excel 365都适配，旧版Excel只需微调细节）。

核心需求回顾 #

你需要：

• 遍历数据列（比如A列）的每个字符串单元格
• 和验证词汇列（比如C列，支持单个或成对词汇）匹配，检查数据单元格是否包含验证词汇
• 匹配到的话在相邻列（比如B列）返回该词汇，无匹配则返回null（空白）

方案1：用ARRAYFORMULA + XLOOKUP（推荐，简洁高效） #

如果你的表格支持XLOOKUP（Google Sheets新版、Excel 365都支持），直接用这个公式：

=ARRAYFORMULA(IF(A2:A="",,XLOOKUP(TRUE,REGEXMATCH(A2:A,"\b"&REGEXESCAPE(C2:C)&"\b"),C2:C,,0,1)))

公式拆解：

• ARRAYFORMULA：让公式一次性处理整列，不用手动下拉填充
• IF(A2:A="",, ...)：空数据单元格直接返回空，避免无效结果
• REGEXMATCH(A2:A,"\b"&REGEXESCAPE(C2:C)&"\b")：
  • REGEXESCAPE：转义验证词汇里的特殊正则字符（比如.、*这类），避免匹配出错
  • \b：单词边界，确保匹配的是完整词汇（比如不会让"cat"匹配到"category"），如果不需要严格完整匹配，去掉\b即可
• XLOOKUP(TRUE, ...)：找到第一个匹配的验证词汇，返回它；没有匹配的话默认返回空白（也就是你要的null）

方案2：用ARRAYFORMULA + INDEX/MATCH（兼容旧版本） #

如果你的表格不支持XLOOKUP，用经典的INDEX/MATCH组合：

=ARRAYFORMULA(IF(A2:A="",,INDEX(C2:C,MATCH(TRUE,REGEXMATCH(A2:A,"\b"&REGEXESCAPE(C2:C)&"\b"),0))))

公式拆解：

• MATCH(TRUE, ..., 0)：找到第一个满足REGEXMATCH为TRUE的验证词汇的位置
• INDEX(C2:C, ...)：根据位置返回对应的验证词汇
• 其余部分和方案1逻辑一致

扩展：返回所有匹配的词汇（不止第一个） #

如果某个数据单元格包含多个验证词汇，想把所有匹配项都列出来，用TEXTJOIN配合FILTER：

=ARRAYFORMULA(IF(A2:A="",,TEXTJOIN(", ",TRUE,FILTER(C2:C,REGEXMATCH(A2:A,"\b"&REGEXESCAPE(C2:C)&"\b")))))

这个公式会把所有匹配的词汇用逗号分隔显示，无匹配则返回空白。

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