Google Sheets Query公式含与逻辑正则表达式无匹配结果排查

问题背景 #

需对450个存在词序变更、大小写变化、新增词汇的商品名称执行模糊匹配，Vlookup因无法解析正则表达式无法满足需求，改用Query公式后无预期结果。已确认IMPORTRANGE可正常导入数据，问题集中在正则搜索条件部分。

当前使用的公式 #

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1tWyHmkm2YAIXpzgs4A0KEFJ5XJhAT61yL_ZuqHtys6g/edit#gid=272207476", "Sheet1!A1:A447"), "select * where Col1 matches ' (?=.*\b"&IFERROR(PROPER(INDEX(SPLIT(A1, " "),1))," ")&"\b) (?=.*\b"&IFERROR(PROPER(INDEX(SPLIT(A1, " "),2))," ")&"\b) (?=.*\b"&IFERROR(PROPER(INDEX(SPLIT(A1, " "),3))," ")&"\b) ' Limit 1" ,0)

测试场景 #

• 搜索键：Ajwain whole 1Kg （A1单元格内容，包含末尾多余空格）
• 预期匹配：Spice Ajwain Seeds Whole 1Kg
• 实际结果：返回N/A错误

正则条件问题排查与修正方案 #

核心问题点 #

1. 未处理多余空格：搜索键末尾的空格会被SPLIT拆分为空值，导致正则中生成(?=.*\b \b)这类无效条件，破坏匹配逻辑。
2. 大小写不匹配：PROPER函数将1Kg转为1kg，但目标值是1Kg，而Google Sheets Query的正则默认区分大小写，直接导致匹配失败。
3. 固定单词数量限制：公式仅处理前3个拆分后的单词，若搜索键单词数超过3，或目标值包含更多关联词汇，会遗漏匹配条件。
4. 单词边界(\b)适配问题：1Kg这类数字+字母的组合，\b的边界匹配可能因字符类型切换出现异常。

修正后的公式 #

=LET(
  search_key, TRIM(A1),
  words, FILTER(SPLIT(search_key, " "), SPLIT(search_key, " ")<>""),
  regex_conditions, BYROW(words, LAMBDA(word, "(?=.*\b"&REGEXREPLACE(word, "[^\w\s]", "\\$0")&"\b)")),
  full_regex, "(?i)"&JOIN("", regex_conditions),
  QUERY(IMPORTRANGE("1tWyHmkm2YAIXpzgs4A0KEFJ5XJhAT61yL_ZuqHtys6g", "Sheet1!A1:A447"), "select * where Col1 matches '"&full_regex&"' limit 1", 0)
)

修正说明 #

• TRIM清理搜索键首尾多余空格，FILTER移除拆分后产生的空值，避免无效正则条件。
• (?i)标记开启不区分大小写匹配，解决大小写差异问题。
• BYROW动态遍历所有拆分后的单词，自动生成对应正向预查条件，不再限制单词数量。
• REGEXREPLACE转义单词中的特殊字符（如括号、点号等），避免正则语法错误。

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