Excel两列组合匹配多规则：如何优化现有公式？

针对你遇到的「Column1需完全匹配参考表值、Column2需包含对应参考字符串」的匹配需求，无需手动为每一行规则添加OR组件，下面提供两种高效实现方式：

1. 兼容全Excel版本的SUMPRODUCT公式 #

适用于所有Excel版本（包括旧版2016及以前），直接在目标单元格输入公式即可：

=SUMPRODUCT(--('REF_Tab'!$A$2:$A$100=[@[Column1]]),--ISNUMBER(SEARCH('REF_Tab'!$B$2:$B$100,[@[Column2]])))>0

公式说明： #

• --('REF_Tab'!$A$2:$A$100=[@[Column1]])：将参考表A列与当前行Column1匹配的结果转换为1（匹配）或0（不匹配）
• --ISNUMBER(SEARCH('REF_Tab'!$B$2:$B$100,[@[Column2]]))：检查当前行Column2是否包含参考表对应B列的字符串，转换为1（包含）或0（不包含）
• SUMPRODUCT计算同时满足两个条件的行数总和，只要总和>0，就说明当前行符合规则，返回TRUE，否则返回FALSE

优化建议： #

• 将$A$2:$A$100和$B$2:$B$100替换为你参考表的实际数据范围（不建议用整列，避免计算空值影响性能）
• 如果需要区分大小写匹配，把SEARCH替换为FIND

2. Excel 365/2021专属动态数组公式 #

如果你使用的是Excel 365或2021版本，可直接用更简洁的数组公式：

=SUM(('REF_Tab'!$A$2:$A$100=[@[Column1]])*ISNUMBER(SEARCH('REF_Tab'!$B$2:$B$100,[@[Column2]])))>0

公式原理和SUMPRODUCT一致，利用365的动态数组特性简化了写法。如果参考表是结构化表格（插入→表格），还可以直接用表名+列名，新增规则时公式会自动识别：

=SUM((Table1[参考Column1]=[@[Column1]])*ISNUMBER(SEARCH(Table1[参考Column2],[@[Column2]])))>0

优势对比 #

• 无需逐行添加AND组件到OR中，一次设置完成
• 参考表新增规则时，只需调整数据范围（或使用结构化表格），公式自动适配
• 避免公式过长导致的维护困难，性能更稳定

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