使用TEXTJOIN/MATCH公式匹配数据时忽略含指定关键词的单元格

原有公式可以实现两表姓名匹配、返回表2中查无对应项的姓名，只需新增一层过滤逻辑排除含Agency的条目即可，调整后即可按要求仅返回Joe Bloggs。

适配Excel 365/2021及以上版本的公式 #

直接使用FILTER多条件判断+UNIQUE去重，逻辑更简洁：

=TEXTJOIN("、",TRUE,UNIQUE(FILTER(
    HrsRoleEchoPay[E-Name of employee],
    (ISERROR(MATCH(HrsRoleEchoPay[E-Name of employee],AwardsFromEchoTbl[Name],FALSE))) * 
    (ISERROR(SEARCH("Agency", HrsRoleEchoPay[E-Name of employee]))),
    ""
)))

逻辑说明 #

• SEARCH("Agency", 姓名)：不区分大小写识别姓名中是否包含Agency关键词，不受后续动态变化的STB内容影响，只要带Agency就会被命中
• 两个ISERROR判断分别对应两个过滤规则：① 姓名在表2的Name列中无匹配 ② 姓名不包含Agency关键词，两个条件同时满足才会被保留
• UNIQUE()：对结果去重，避免同一个姓名多次出现
• TEXTJOIN第一参数为姓名之间的分隔符，可根据需要自行修改，传空字符串""就会直接拼接所有结果

兼容旧版Excel的数组公式 #

如果使用的是不支持动态数组的旧版Excel，输入以下公式后需按Ctrl+Shift+Enter三键确认运行：

=TEXTJOIN("、",TRUE,IF(
    (ISERROR(MATCH(HrsRoleEchoPay[E-Name of employee],AwardsFromEchoTbl[Name],FALSE)))*
    (ISERROR(SEARCH("Agency",HrsRoleEchoPay[E-Name of employee]))),
    IF(MATCH(HrsRoleEchoPay[E-Name of employee],HrsRoleEchoPay[E-Name of employee],0)=ROW(HrsRoleEchoPay[E-Name of employee])-ROW(HrsRoleEchoPay[#Headers]),HrsRoleEchoPay[E-Name of employee],""),
    ""
))

按照给出的示例数据运行，所有Agency STB Agency STB格式的条目都会被过滤，最终返回结果为Joe Bloggs。

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