如何实现单列字符串与5列3724行列表匹配并返回单元格地址？

针对5列3724行的大数据量，以及单元格含方括号导致的公式错误、逗号缺失问题，可通过以下方案解决：

核心公式（Excel 365/2021及以上版本） #

使用TEXTJOIN+FILTER组合，同时转义方括号避免通配符冲突：

=TEXTJOIN(", ", TRUE, FILTER(A1:E3724, ISNUMBER(SEARCH("目标文本", SUBSTITUTE(SUBSTITUTE(A1:E3724, "[", "[[]"), "]", "[]]"))), ""))

公式说明 #

• 方括号转义：SUBSTITUTE(SUBSTITUTE(A1:E3724, "[", "[[]"), "]", "[]]") 将单元格中的[替换为[[、]替换为]]，避免Excel把方括号当作通配符解析，确保SEARCH能正确匹配含方括号的内容
• 高效筛选：FILTER直接从5列3724行的范围中提取包含目标文本的单元格，比旧版数组公式效率更高，适配大数据量
• 规范拼接：TEXTJOIN(", ", TRUE, ...) 自动忽略空值，用, 分隔匹配结果，避免逗号缺失或多余分隔符

旧版Excel兼容方案（无FILTER/TEXTJOIN） #

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

=TEXTJOIN(", ", TRUE, IF(ISNUMBER(SEARCH("目标文本", SUBSTITUTE(SUBSTITUTE(A1:E3724, "[", "[[]"), "]", "[]]"))), A1:E3724, ""))

注：大数据量下数组公式效率较低，优先推荐365/2021版本方案

常见问题排查 #

1. 仍出现#VALUE!：检查拼接结果是否超过单个单元格最大字符数（32767），若超出需拆分到多个单元格，或使用VBA批量处理
2. 目标文本含方括号：需同步转义目标文本，例如搜索[关键词]需写成[[关键词]]

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