带日期值的多列Index Match数组公式问题求助

问题根源 #

你之前的公式失效核心原因：

• INDEX 在 ARRAYFORMULA 中直接调用时，只会返回 MATCH 数组的第一个匹配结果，无法逐行对应 Name-Course 组合
• 未使用迭代函数（如 BYROW/BYCOL）处理每行的独立匹配逻辑

完整实现步骤 #

1. 动态生成日期列标题 #

在 Summary 表的 C1 单元格 输入以下公式，自动提取 Grade Data 中的唯一日期并转置为横向列标题，新增日期时会自动更新：

=TRANSPOSE(UNIQUE(FILTER(GradeData!$A:$A, GradeData!$A:$A<>"")))

2. 动态匹配对应成绩 #

方案1：XLOOKUP + BYROW/BYCOL（推荐，逻辑清晰）

在 Summary 表的 C2 单元格 输入以下公式，自动遍历所有日期列和学生行，匹配对应成绩：

=BYCOL(C1:1, LAMBDA(date_col, BYROW(A2:B, LAMBDA(name_course_row, 
  IF(COUNTA(name_course_row)=0, "", 
    XLOOKUP(TEXT(date_col,"yyyy-mm-dd")&name_course_row[0]&name_course_row[1], 
      TEXT(GradeData!$A:$A,"yyyy-mm-dd")&GradeData!$B:$B&GradeData!$C:$C, 
      GradeData!$D:$D, ""
    )
  )
))))

• 无需手动填充，公式会自动扩展到所有行和列
• 空的 Name-Course 行自动返回空值
• 统一用 TEXT(..., "yyyy-mm-dd") 转换日期，避免格式不匹配导致的匹配失败

方案2：改进原 INDEX-MATCH 公式

如果你偏好使用 INDEX-MATCH，可在 Summary 表的 C2 单元格 输入以下公式，然后向右拖动填充到所有日期列：

=BYROW($A2:$B, LAMBDA(row, 
  IF(COUNTA(row)=0, "", 
    INDEX(GradeData!$D:$D, 
      MATCH(TEXT(C$1,"yyyy-mm-dd")&row[0]&row[1], 
        TEXT(GradeData!$A:$A,"yyyy-mm-dd")&GradeData!$B:$B&GradeData!$C:$C, 0
      )
    )
  )
))

注意事项 #

• 确保 Grade Data 中的 Date 列是纯日期格式（不是文本），否则 TEXT 转换会出错
• 若存在重复的 Name-Course-Date 组合，公式会返回第一个匹配的成绩；如需处理重复数据，可改用 QUERY 函数筛选
• 新增数据到 Grade Data 后，Summary 表会自动刷新日期列和成绩数据

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