Excel多城市单元格跨表查询球队并去重方案咨询

适用场景 #

表1A列单元格包含单个或多个城市，表2存储城市-球队对应关系，需自动匹配并生成去重后的球队列表，结果随数据源更新。

公式实现（Excel 365/2021 及以上版本） #

在表1的B列（对应A列城市）输入以下动态数组公式：

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(表2!$B:$B, ISNUMBER(SEARCH(表2!$A:$A, A1)))))

公式拆解

• SEARCH(表2!$A:$A, A1)：检测表2中每个城市是否出现在表1当前单元格（A1）的城市文本中，返回匹配位置或错误值
• ISNUMBER(...)：将SEARCH结果转换为布尔值，匹配成功为TRUE，失败为FALSE
• FILTER(表2!$B:$B, ...)：筛选出表2中所有匹配城市对应的球队
• UNIQUE(...)：自动去除筛选结果中的重复球队名称（解决跨城市关联的重复球队问题，如弗里斯科与达拉斯的重复球队）
• TEXTJOIN(", ", TRUE, ...)：将去重后的球队用「逗号+空格」连接成字符串，TRUE参数忽略空值

旧版Excel（无动态数组支持）解决方案 #

若使用Excel 2019及以下版本，需使用数组公式（输入后按Ctrl+Shift+Enter确认）：

=TEXTJOIN(", ", TRUE, IFERROR(INDEX(表2!$B:$B, MATCH(0, COUNTIF($B$1:B1, 表2!$B:$B)+IF(ISNUMBER(SEARCH(表2!$A:$A, A1)), 0, 1), 0)), ""))

公式说明

• COUNTIF($B$1:B1, 表2!$B:$B)：记录已提取到当前单元格上方的球队，避免重复提取
• IF(ISNUMBER(...), 0, 1)：标记匹配城市的球队，未匹配的标记为1，让MATCH跳过
• MATCH(0, ..., 0)：找到第一个未被提取且匹配城市的球队位置
• INDEX(表2!$B:$B, ...)：取出对应位置的球队名称
• IFERROR(..., "")：处理无匹配结果的情况，返回空值

使用注意事项 #

• 表2的城市列需准确对应球队，避免无效数据
• 表1A列的多城市建议用统一分隔符（如逗号），确保SEARCH能正确识别
• 公式下拉填充后，会随表1、表2的数据源变化自动更新结果

内容的提问来源于stack exchange，提问作者Mohammad Usman Aijaz