Excel条件格式：高亮含Checklist同行列姓、名子串的用户姓名单元格

数据情况 #

Responses工作表（用户反馈） #

Checklist工作表（有效姓名列表） #

需求 #

高亮Responses表User-Provided Name列中，同时包含Checklist表同一行的姓氏和名字子串的单元格。根据示例数据，John Smith和Smith, John需匹配，Jimmer McTest和John McDougal不应匹配。

原公式问题 #

之前在A列使用的条件格式公式存在逻辑错误：

= 
AND 
( 
    SUMPRODUCT 
    ( 
        --ISNUMBER 
        ( 
            SEARCH 
            ( 
                FILTER 
                ( 
                    'Checklist'!$B$2:$B$9999,'Checklist'!$B$2:$B$9999<>"" 
                ),A1 
            ) 
        ) 
    )+SUMPRODUCT 
    ( 
        --ISNUMBER 
        ( 
            SEARCH 
            ( 
                FILTER 
                ( 
                    'Checklist'!$C$2:$C$9999,'Checklist'!$C$2:$C$9999<>"" 
                ),A1 
            ) 
        ) 
    )=2, 
    D1<>"Some-Disqualifier" 
) 

错误场景：

• 名和姓来自Checklist表不同行时，误判为匹配（比如示例中的John McDougal）
• 单元格中出现两次名字但无对应姓氏时，误判为匹配
• 单元格中出现两次姓氏但无对应名字时，误判为匹配（比如Sally Smith）

同时需要支持Responses表数据持续增长的动态范围。

解决方案 #

方式1：条件格式公式（直接应用于A列） #

使用以下公式作为条件格式规则，确保只有当Checklist表中存在某一行的姓氏和名字同时出现在当前单元格时，才返回TRUE：

=AND(SUMPRODUCT(--(ISNUMBER(SEARCH('Checklist'!$B$2:$B$9999,A1))*ISNUMBER(SEARCH('Checklist'!$C$2:$C$9999,A1)))>0,D1<>"Some-Disqualifier")

如果需要过滤Checklist表中的空行，可调整为：

=AND(SUMPRODUCT(--(ISNUMBER(SEARCH(FILTER('Checklist'!$B$2:$B$9999,'Checklist'!$B$2:$B$9999<>""),A1))*ISNUMBER(SEARCH(FILTER('Checklist'!$C$2:$C$9999,'Checklist'!$C$2:$C$9999<>""),A1)))>0,D1<>"Some-Disqualifier")

原理：将同一行的姓氏和名字的搜索结果相乘，只有两者都匹配时才会得到1，SUMPRODUCT求和后大于0，说明存在符合条件的完整姓名行。

方式2：溢出函数辅助列（自动适配增长的数据） #

在Responses表的空白列（比如E1单元格）输入以下溢出公式，自动生成所有行的验证结果：

=BYROW(A:A,LAMBDA(name,IF(name="","",AND(SUMPRODUCT(--(ISNUMBER(SEARCH('Checklist'!$B$2:$B$9999,name))*ISNUMBER(SEARCH('Checklist'!$C$2:$C$9999,name)))>0,OFFSET(name,0,3)<>"Some-Disqualifier"))))

之后设置条件格式规则：当E列对应单元格为TRUE时，高亮A列的单元格。
原理：BYROW遍历A列每个单元格，OFFSET获取对应D列的 disqualifier值，自动处理新增行的验证。

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