Excel中如何判断长字符串数组元素是否匹配短字符串数组？

需求 #

我在Excel中有两组字符串数组，需要判断长字符串数组的每个元素是否能匹配短字符串数组中的任意元素，要求返回True（匹配）或False（不匹配）。

数组示例 #

长数组（LongStrings工作表A列） #

A1: 7-Zip 22.01 (x64)
A2: Adobe Acrobat
A3: Adobe Acrobat (64-bit)
A4: Adobe Acrobat DC
A5: Adobe Acrobat Reader

短数组（ShortStrings工作表A列） #

A1: 7-Zip
A2: ABS PDF Install
A3: Adobe Acrobat Reader
A4: Adobe Genuine Service

匹配规则 #

• 长数组A1应匹配短数组A1（长元素包含短元素）
• 长数组A5应匹配短数组A3（长元素与短元素完全一致）
• 长数组A2不应匹配短数组A3（长元素不包含短元素）

已尝试的公式及问题 #

我试过以下公式：

=MATCH(LOWER("*"&ShortStrings!A2&"*"),LOWER("*"&LongStrings!A2:A10&"*"),0)

返回结果不符合预期：

A1 7-Zip 22.01 (x64) - 无匹配（应匹配短数组A1）
A2 Adobe Acrobat - 无匹配（正确）
A3 Adobe Acrobat (64-bit) - 无匹配（正确）
A4 Adobe Acrobat DC - 无匹配（正确）
A5 Adobe Acrobat Reader - 匹配到A3（正确）

解决方案 #

原公式的匹配逻辑颠倒，正确逻辑是检查长数组元素是否包含短数组中的任意元素，以下是可行的公式：

方法1：SUMPRODUCT+SEARCH（不区分大小写） #

在长数组对应单元格（如LongStrings!B1）输入公式后下拉填充：

=SUMPRODUCT(--ISNUMBER(SEARCH(ShortStrings!$A$1:$A$4, LongStrings!A1)))>0

• SEARCH：在长元素中查找短元素，不区分大小写，找到返回位置，否则返回错误
• ISNUMBER：将结果转为布尔值
• --：把布尔值转为1/0
• SUMPRODUCT：统计符合条件的短元素数量，大于0则返回True

方法2：OR+SEARCH（数组公式，适用于Excel 365及以后） #

=OR(ISNUMBER(SEARCH(ShortStrings!$A$1:$A$4, LongStrings!A1)))

无需手动按数组快捷键，直接回车即可生效。

方法3：区分大小写的精确匹配 #

如果需要严格区分大小写的完全匹配：

=SUMPRODUCT(--EXACT(ShortStrings!$A$1:$A$4, LongStrings!A1))>0

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