Google Sheets中ARRAYFORMULA嵌套INDEX/MATCH失效问题解决

错误原因 #

你的数组公式存在两个核心问题：

• AND函数不支持数组逐行判断：AND(NOT(ISBLANK(...)))会将整个区域的条件做逻辑与，返回单个布尔值，而非逐行验证，导致公式无法对部分行生效
• INDEX(MATCH)的数组适配问题：当MATCH(A2:A, ..., -1)返回数组时，普通ARRAYFORMULA下的INDEX无法自动将每个索引值与对应行绑定，导致始终返回第一行的C2值

方案1：修复原逻辑的数组公式（BYROW遍历） #

使用BYROW逐行处理A列日期，确保每个日期单独执行原单元格公式的逻辑，避免数组运算的索引错误：

=ARRAYFORMULA(
  BYROW(A2:A, LAMBDA(date,
    IF(ISBLANK(date),,
      IFNA(
        VLOOKUP(date, 'USD_TRY Historical Data'!A2:B, 2, FALSE),
        INDEX('USD_TRY Historical Data'!C:C, MATCH(date, 'USD_TRY Historical Data'!A:A, -1))
      )
    )
  ))

关键修正： #

• 用BYROW+LAMBDA遍历每个日期，还原原单元格公式的逐行逻辑
• 移除错误的AND判断，改用IF(ISBLANK(date),, ...)精准处理空行
• 简化引用范围，利用MATCH自动忽略表头和空行的特性，直接引用整列

方案2：更简便的XLOOKUP替代方案 #

Google Sheets原生的XLOOKUP支持数组运算和降序近似匹配，可一步实现需求，无需嵌套多层函数：

=ARRAYFORMULA(
  XLOOKUP(
    A2:A,
    'USD_TRY Historical Data'!A:A,
    'USD_TRY Historical Data'!B:B,
    XLOOKUP(
      A2:A,
      'USD_TRY Historical Data'!A:A,
      'USD_TRY Historical Data'!C:C,
      ,
      -1
    ),
    0
  )
)

逻辑说明： #

1. 外层XLOOKUP：精确匹配A列日期，返回对应Price（B列）
2. 未匹配到的情况下，内层XLOOKUP用-1参数（降序近似匹配），找到小于等于目标日期的最近有效日期，返回对应Open（C列）
3. 原生支持数组运算，写法更简洁，性能更稳定

内容的提问来源于stack exchange，提问作者Shah-G