Excel可用公式在Google Sheets报错：FILTER范围大小不匹配求解

错误原因 #

Google Sheets 与 Excel 对跨表命名范围的数组处理逻辑存在差异：Excel 会自动将多行单列的命名范围作为数组遍历，但 Google Sheets 中直接使用跨表命名范围时，SEARCH 函数可能仅识别为单个单元格，导致 FILTER 函数的条件范围与数据范围行数不匹配，触发大小错误。

解决方法 #

• 检查命名范围定义
  确认跨表命名范围 People2 是单列多行的连续区域（如 Sheet2!A1:A211），而非单个单元格或不连续区域。通过「数据>命名范围」查看并修正引用，避免范围边界错误。

• 显式强制数组遍历
  用函数强制将跨表命名范围转为数组，让 SEARCH 遍历每一个元素：

  • 方案1：使用 TOCOL 扁平化范围

    =TEXTJOIN(", ", 1, FILTER(People2, ISNUMBER(SEARCH(TOCOL(People2), D2)), ""))
    

    TOCOL 会把跨表范围转为标准单列数组，确保 SEARCH 能逐个匹配。
  • 方案2：用 ARRAYFORMULA 包裹 SEARCH

    =TEXTJOIN(", ", 1, FILTER(People2, ISNUMBER(ARRAYFORMULA(SEARCH(People2, D2))), ""))
    

    ARRAYFORMULA 强制 SEARCH 对 People2 的每一行执行匹配，返回对应布尔数组供 FILTER 使用。

• 直接引用跨表区域排查
  如果命名范围仍有问题，直接替换为跨表区域路径验证：

  =TEXTJOIN(", ", 1, FILTER(Sheet2!A1:A211, ISNUMBER(SEARCH(Sheet2!A1:A211, D2)), ""))
  

  若此公式正常运行，说明问题出在命名范围定义，重新检查范围引用是否准确。

• 过滤空单元格
  若 People2 包含空单元格，会导致 FILTER 范围大小异常，可添加非空判断：

  =TEXTJOIN(", ", 1, FILTER(People2, ISNUMBER(SEARCH(TOCOL(People2), D2)) * (People2 <> ""), ""))
  

内容的提问来源于stack exchange，提问作者N. Adam