如何优化Excel中多行单元格匹配指定表格值并标记的公式

优化方案 #

适用Microsoft 365/Excel 2021及以上版本 #

直接用动态数组公式，无需手动调整匹配范围，新增COMP_VAL项后自动生效：

=IF(SUM(--ISNUMBER(SEARCH(COMP_VAL!$A:$A,A1))),"X","")

• 公式说明：SEARCH会自动遍历COMP_VAL表A列所有非空值与A1匹配，ISNUMBER判断是否命中，求和后只要有命中项结果就大于0，IF返回X
• 如需区分大小写匹配，把SEARCH替换为FIND即可
• 如需避免部分匹配误判（比如匹配到value AA这类包含目标值的内容），可以利用多行单元格的换行符CHAR(10)做整行匹配，公式修改为：

=IF(SUM(--ISNUMBER(SEARCH(CHAR(10)&COMP_VAL!$A:$A&CHAR(10),CHAR(10)&A1&CHAR(10)))),"X","")

适用Excel 2019及以下旧版本 #

用兼容性更好的SUMPRODUCT公式，无需数组回车即可生效，同样支持自动识别COMP_VAL的有效匹配范围：

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(COMP_VAL!$A$1:INDEX(COMP_VAL!$A:$A,COUNTA(COMP_VAL!$A:$A)),A1))),"X","")

• 公式说明：INDEX+COUNTA会自动定位COMP_VAL表A列最后一个非空单元格，不用手动硬编码匹配范围，新增匹配项无需改公式

原有公式问题说明 #

你当前使用的公式存在两个明显问题：

1. 硬编码匹配COMP_VAL的固定单元格，新增匹配项需要手动修改公式，扩展性极差
2. 存在语法错误，括号未闭合，且第二个FIND错误引用了A2，批量填充时会出现匹配错误

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