Excel单元格分隔值比对求助：判断D2值是否存在于C2

需求说明 #

• C2、D2单元格存着以|分隔的文本（也可能是单个无分隔符的值）
• 要在E2写公式，判断D2里的任意一个值是否能在C2中找到，返回True或False
• 原公式在值顺序不同、多重复值场景下出错，还容易出现部分匹配的误判（比如把"abc"识别成包含在"abcd"里）

方案1：兼容所有Excel版本 #

这个公式靠精确匹配避免误判，不用依赖新版函数：

=SUMPRODUCT(--(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(D2,"|",REPT(" ",LEN(D2))),ROW(INDIRECT("1:"&LEN(D2)-LEN(SUBSTITUTE(D2,"|",""))+1))*LEN(D2)-LEN(D2)+1,LEN(D2))),TRIM(MID(SUBSTITUTE(C2,"|",REPT(" ",LEN(C2))),ROW(INDIRECT("1:"&LEN(C2)-LEN(SUBSTITUTE(C2,"|",""))+1))*LEN(C2)-LEN(C2)+1,LEN(C2))),0)))>0

逻辑拆解：

1. 用SUBSTITUTE+REPT+MID把|分隔的文本拆成单个值，TRIM清掉拆分时产生的多余空格
2. MATCH做精确匹配（第三个参数设为0），找到匹配值返回位置，没找到返回错误
3. ISNUMBER把匹配结果转成布尔值，--再转成数字（匹配到是1，没匹配到是0）
4. SUMPRODUCT把所有数字求和，只要总和大于0，就说明有匹配项，返回True，否则False

方案2：适用于Excel 365/2021（支持TEXTSPLIT） #

新版Excel有更简洁的写法，直接用TEXTSPLIT拆分文本后判断：

=OR(ISNUMBER(XLOOKUP(TEXTSPLIT(D2,"|"),TEXTSPLIT(C2,"|"),,0)))

或者用COUNTIF实现：

=COUNTIF(TEXTSPLIT(C2,"|"),"@"&TEXTSPLIT(D2,"|")&"@")>0

逻辑拆解：

1. TEXTSPLIT直接把|分隔的文本拆成数组
2. 第一个公式里，XLOOKUP逐个匹配D2拆分出的数组和C2的数组，ISNUMBER判断是否存在匹配，OR只要有一个匹配就返回True
3. 第二个公式用COUNTIF统计匹配的数量，只要数量大于0就返回True

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