Excel如何引用单元格范围作为子串列表，匹配指定单元格内容后取值

解决方案 #

全版本兼容公式（支持Excel 2007及以上，无需手动输入数组公式） #

逻辑1：和原有硬编码公式完全一致（同一单元格匹配到多个子串时，返回对应倍数的B列值）

=SUMPRODUCT(--ISNUMBER(SEARCH(Fish,A2)))*B2

逻辑2：仅判断是否匹配到至少1个子串，匹配到返回B列值，未匹配返回0

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Fish,A2)))>0,B2,0)

常见问题说明 #

• 你之前写的数组公式{=IF(ISNUMBER(SEARCH(Fish,A2)),B2,0)}仅返回第一个子串的匹配结果，是因为公式会返回和Fish区域大小一致的数组，未做汇总的情况下默认只取第一个元素的值
• 你尝试的COUNTIF方案逻辑是反向判断子串是否存在于目标范围，和你的需求不匹配，所以无法生效
• 如果需要区分大小写的匹配，把公式里的SEARCH替换为FIND即可
• 注意Fish子串存储区域不要包含空白单元格，否则空白会被判定为匹配所有单元格，导致结果错误
• 如果你使用的是Excel 365/2021及以上支持动态数组的版本，把公式里的SUMPRODUCT换成SUM也可以达到相同效果

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