求ArrayFormula解决方案：查找搜索词在目标列首次出现的行号

核心公式实现 #

针对A列的搜索词，批量返回其在E列字符串中首次出现的行号，同时适配动态增删改的数据集，直接用以下公式即可：

=ArrayFormula(IF(A2:A="",,XLOOKUP(TRUE,REGEXMATCH(E2:E,A2:A),ROW(E2:E),"",0,1)))

公式细节说明 #

• ArrayFormula：触发整列批量计算，不用手动下拉填充
• IF(A2:A="",, ...)：自动跳过A列空单元格，避免无效计算结果
• REGEXMATCH(E2:E,A2:A)：逐个检查A列搜索词是否包含在E列对应行的字符串里，输出布尔值数组
• XLOOKUP(TRUE, ...)：在布尔数组中定位第一个TRUE的位置，返回对应的E列行号；无匹配时返回空字符串
• ROW(E2:E)：提取E列每行的行号
• 末尾的0,1参数：指定精确匹配+从前往后查找，确保返回首次出现的行号

针对大数据集的性能优化 #

面对25K-30K行搜索词、5K-10K行目标字符串的场景，可优化计算效率：

• 收缩数据范围：把E2:E替换成E2:INDEX(E:E,COUNTA(E:E))，只计算E列有内容的行，减少冗余计算
• 去重后匹配：如果A列有大量重复搜索词，先通过UNIQUE(A2:A)生成唯一词列表，匹配完成后再用VLOOKUP映射回原列，减少重复计算量
• 关闭不必要的扩展功能：暂时关闭Google Sheets的协作历史记录等非核心功能，降低后台负载

对比其他方案的优势 #

• 替代QUERY：无需下拉，一次公式覆盖整列，数据增删改后自动更新结果
• 解决MATCH/VLOOKUP局限：通过REGEXMATCH实现字符串内的部分匹配，而非只能精确匹配
• 平衡性能与完整性：XLOOKUP的查找逻辑高效，不会出现漏匹配或大规模卡顿的问题

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