Excel公式可正常运行，迁移至Google Sheets后失效求助

问题说明 #

原Excel公式可正常筛选并返回匹配数据：

=IFERROR(
  INDEX(
    'Master Entries'!A$3:A$250, 
    AGGREGATE(
      15,
      6,
      (ROW('Master Entries'!A$3:A$250)-ROW('Master Entries'!A$3)+1)
      /('Master Entries'!$E$3:$E$250=$C$2)
      /('Master Entries'!$F$3:$F$250="x"), 
      ROW(A1)
    )
  ), 
  ""
)

但上传至Google Sheets后，系统自动添加ARRAY_CONSTRAIN和ARRAYFORMULA包裹公式，导致公式失效无结果：

=ARRAY_CONSTRAIN(
  ARRAYFORMULA(
    IFERROR(
      INDEX(
        'Master Entries'!A$3:A$250, 
        AGGREGATE(
          15,
          6,
          (ROW('Master Entries'!A$3:A$250)-ROW('Master Entries'!A$3)+1)
          /('Master Entries'!$E$3:$E$250=$C$2)
          /('Master Entries'!$F$3:$F$250="x"), 
          ROW(A1)
        )
      ), 
      ""
    )
  ), 
  1, 
  1
)

失效原因 #

Google Sheets的AGGREGATE函数对数组运算的支持逻辑与Excel不同，无法像Excel那样自动处理除数为0的错误并返回有效索引；同时系统自动添加的ARRAYFORMULA会破坏ROW(A1)的动态引用逻辑，导致无法按顺序返回第N个匹配项。

解决方案 #

使用Google Sheets原生兼容的写法替代，以下两种方案均可：

方案1：FILTER + INDEX（简洁直观） #

该方案先通过FILTER筛选出所有符合条件的A列数据，再用INDEX按顺序返回第N个结果（下拉公式时ROW(A1)会自动变为ROW(A2)、ROW(A3)等）：

=IFERROR(INDEX(FILTER('Master Entries'!A$3:A$250, 'Master Entries'!$E$3:$E$250=$C$2, 'Master Entries'!$F$3:$F$250="x"), ROW(A1)), "")

方案2：SMALL + IF 模拟AGGREGATE逻辑 #

该方案还原原Excel公式的逻辑，用SMALL配合IF筛选有效行号，替代Excel中AGGREGATE(15,6,...)的功能：

=IFERROR(INDEX('Master Entries'!A$3:A$250, SMALL(IF(('Master Entries'!$E$3:$E$250=$C$2)*('Master Entries'!$F$3:$F$250="x"), ROW('Master Entries'!A$3:A$250)-ROW('Master Entries'!A$3)+1), ROW(A1))), "")

注：输入该公式后直接按回车即可，Google Sheets会自动处理数组运算，无需手动触发数组公式。

批量自动填充（可选） #

如果希望一次性生成所有匹配结果，无需手动下拉公式，可使用ARRAYFORMULA包裹方案1的公式：

=ARRAYFORMULA(IFERROR(INDEX(FILTER('Master Entries'!A$3:A$250, 'Master Entries'!$E$3:$E$250=$C$2, 'Master Entries'!$F$3:$F$250="x"), ROW(A1:A248)), ""))

这里A1:A248对应原数据的行数范围（250-3+1=248），可根据实际数据行数调整。

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