如何判断指定单元格区域的值是否为另一单元格的子字符串？

需求：针对B列的每个单元格，判断A列中是否存在任意值是该B单元格的子字符串，结果输出在C列（Yes/No），示例如下：

公式实现 #

在C2单元格输入以下公式，下拉填充至所有需要判断的行：

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($A$2:$A$5,B2)))>0,"Yes","No")

公式说明 #

• SEARCH($A$2:$A$5,B2)：遍历A列指定区域的每个值，检查其是否为当前B单元格的子字符串，匹配成功返回字符位置，失败返回错误值
• ISNUMBER(...)：将返回的位置转为逻辑值TRUE，错误值转为FALSE
• --：把逻辑值TRUE/FALSE转换为数值1/0
• SUMPRODUCT(...)：对转换后的数值求和，若结果大于0，说明存在至少一个匹配项，返回"Yes"，否则返回"No"

优化（排除A列空白单元格） #

如果A列存在空白单元格，可使用以下公式避免误判：

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH($A$2:$A$5,B2))*($A$2:$A$5<>"")))>0,"Yes","No")

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