Google Sheets列表内部分匹配查找的高效替代公式求助

原公式index(Categories!$B$2:$B,match(1,search("*"&Categories!$B$2:$B&"*",$D2),0))可实现指定区域的部分匹配分类查找，但因逐单元格执行数组运算，数据量较大时会显著拖慢表格速度，以下是几种更高效的替代方案：

方案1：批量数组公式（推荐，一次性处理整列） #

=ARRAYFORMULA(IFERROR(VLOOKUP(REGEXEXTRACT(D2:D, TEXTJOIN("|", TRUE, Categories!B2:B)), Categories!B2:B, 1, FALSE), "未匹配"))

原理： #

1. 用TEXTJOIN将所有分类拼接成正则表达式的备选匹配列表（用|分隔）
2. REGEXEXTRACT从D列内容中提取第一个匹配的分类关键词
3. VLOOKUP精准匹配返回对应分类，ARRAYFORMULA实现整列批量计算，避免逐单元格重复运算

方案2：QUERY函数方案 #

=ARRAYFORMULA(IF(D2:D="",,IFERROR(QUERY(Categories!B2:B, "select B where '"&D2:D&"' contains B limit 1", 0), "未匹配")))

原理： #

利用QUERY函数的contains条件直接查找包含在D列内容中的分类，limit 1取第一个匹配结果，结合ARRAYFORMULA批量应用，运算效率远高于原公式的数组匹配逻辑。

方案3：BYROW+REGEXMATCH方案 #

=BYROW(D2:D, LAMBDA(cell, IF(cell="",,IFERROR(INDEX(Categories!B2:B, MATCH(TRUE, REGEXMATCH(cell, Categories!B2:B), 0)), "未匹配"))))

原理： #

用BYROW遍历D列每个单元格，通过REGEXMATCH替代原公式的SEARCH执行匹配，逻辑更简洁，且Google Sheets对BYROW的运算优化优于原公式的嵌套数组匹配。

注意事项： #

• 若分类列表中包含正则特殊字符（如. * + ? ^ $等），需用REGEXESCAPE处理，例如将方案1中的Categories!B2:B替换为ARRAYFORMULA(REGEXESCAPE(Categories!B2:B))，避免匹配异常
• 所有批量公式仅需在D列对应结果列的第一行输入，自动覆盖整列，切勿逐单元格重复输入公式，这是提升性能的核心

内容的提问来源于stack exchange，提问作者Mr.YJY