如何在Excel中检测数据相同值并输出匹配/不匹配结果？

核心需求 #

检测A列（含数组格式数据）与B列（逗号分隔值）是否存在相同值，存在则对应C列输出match，不存在输出not match。示例：

A1=[1,2,3,4,5]，B1=10,11,9 → C1输出not match
A2=[7,8,9]，B2=8,5,2 → C2输出match

方法1：适用于Excel 365/2021（支持动态数组） #

利用TEXTSPLIT拆分字符串，结合XMATCH判断是否有匹配项：
在C1单元格输入公式，下拉填充即可：

=IF(SUMPRODUCT(--(ISNUMBER(XMATCH(TEXTSPLIT(A1,{",","}","["),TEXTSPLIT(B1,",")))))>0,"match","not match")

公式说明：

• TEXTSPLIT(A1,{",","}","[")：拆分A1的数组格式，去掉[、]和逗号，提取单个数值
• TEXTSPLIT(B1,",")：拆分B1的逗号分隔值为单个数值
• XMATCH：查找两列拆分后的值是否有匹配，返回位置或错误值
• ISNUMBER+--：将匹配结果转为数字（匹配为1，不匹配为0）
• SUMPRODUCT：求和所有匹配结果，大于0则说明存在交集

方法2：适用于旧版Excel（不支持动态数组） #

用FILTERXML替代TEXTSPLIT实现字符串拆分，公式如下：

=IF(SUMPRODUCT(--(ISNUMBER(MATCH(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"[",""),"]",""),",","</s><s>")&"</s></t>","//s"),FILTERXML("<t><s>"&SUBSTITUTE(B1,",","</s><s>")&"</s></t>","//s"),0))))>0,"match","not match")

公式说明：

• FILTERXML(...)：通过构造XML结构，将A/B列的字符串拆分为单个数值列表
• MATCH：查找两列表是否有交集，逻辑与方法1一致

注意事项 #

• 确保A列的数组格式符号[/]为英文半角，B列的分隔符为英文逗号
• 如果数据中包含空格，需在SUBSTITUTE中添加空格替换，例如将SUBSTITUTE(B1,",","</s><s>")改为SUBSTITUTE(SUBSTITUTE(B1," ",""),",","</s><s>")

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