如何在Google Sheets中对比含多值单元格，判断是否存在匹配值

要实现「两个单元格存在任意相同值则显示match，无共同值则显示not match」的需求，可以通过以下几种公式方法实现，同时兼容带方括号（如[1,2,3]）和纯逗号分隔（如5,6,7）的单元格格式：

方法一：COUNTIF + SPLIT 组合公式 #

在需要输出结果的单元格（如C1）中输入以下公式：

=IF(COUNTIF(SPLIT(SUBSTITUTE(SUBSTITUTE(A1,"[",""),"]",""),","), "*"&SPLIT(B1,",")&"*")>0, "match", "not match")

公式拆解： #

• SUBSTITUTE(SUBSTITUTE(A1,"[",""),"]","")：移除A1单元格中的方括号，将[1,2,3,4]转换为1,2,3,4
• SPLIT(..., ",")：将逗号分隔的字符串拆分为单个值的数组
• COUNTIF(数组A, "*"&数组B&"*")：统计数组A中与数组B值完全匹配的数量（通配符*避免部分匹配，比如防止12和1被误判为匹配）
• IF(计数>0, "match", "not match")：根据计数结果返回对应文本

方法二：REGEXMATCH + TEXTJOIN 简洁公式 #

如果偏好更简洁的写法，可使用正则匹配：

=IF(REGEXMATCH(TEXTJOIN("|", TRUE, SPLIT(SUBSTITUTE(SUBSTITUTE(A1,"[",""),"]",""),",")), TEXTJOIN("|", TRUE, SPLIT(B1,","))), "match", "not match")

公式拆解： #

• TEXTJOIN("|", TRUE, ...)：将拆分后的单个值用|连接，生成正则表达式的匹配模式（比如1|2|3|4）
• REGEXMATCH(模式, 目标值)：判断目标值是否与模式中的任意值匹配

处理含空格的情况 #

如果单元格中的值带有空格（如1, 2, 3），需要用TRIM去除空格，修改后的公式如下：

=IF(COUNTIF(ARRAYFORMULA(TRIM(SPLIT(SUBSTITUTE(SUBSTITUTE(A1,"[",""),"]",""),","))), "*"&ARRAYFORMULA(TRIM(SPLIT(B1,",")))&"*")>0, "match", "not match")

示例验证 #

示例1：A1值为[1,2,3,4]、B1值为5,6,7时，C1返回not match
示例2：A2值为[10,12,14,16]、B2值为18,14,20时，C2返回match

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