寻求Google Sheets多条件优先级课程匹配公式解决方案

核心公式 #

将以下公式输入到Schedule表格的B2单元格（假设A列是学生ID，第一行是时段），拖动填充即可覆盖整个需计算区域：

=LET(
  student_id, $A2,
  current_block, REGEXREPLACE(B$1, "^[FS]", "B"),
  student_age, RIGHT(student_id, 2)*1,
  student_col, MATCH(student_id, Survey!$F$1:$ZZ$1, 0),
  IF(ISNA(student_col), "NO STUDENT",
    LET(
      student_rank_col, OFFSET(Survey!$F:$F, 0, student_col-1),
      get_match, LAMBDA(rank_num,
        LET(
          course, XLOOKUP(rank_num, student_rank_col, Survey!$B:$B, "", 0, 1),
          block, XLOOKUP(rank_num, student_rank_col, Survey!$E:$E, "", 0, 1),
          age_range, XLOOKUP(rank_num, student_rank_col, Survey!$C:$C, "", 0, 1),
          min_age, IFERROR(INDEX(SPLIT(age_range, "-"), 1)*1, 0),
          max_age, IFERROR(INDEX(SPLIT(age_range, "-"), 2)*1, 99),
          IF(AND(
            REGEXREPLACE(block, "^[FS]", "B")=current_block,
            student_age>=min_age,
            student_age<=max_age
          ), rank_num&": "&course, "")
        )
      ),
      match_6, get_match(6),
      match_5, get_match(5),
      match_4, get_match(4),
      match_3, get_match(3),
      result, IFNA(INDEX(FILTER({match_6, match_5, match_4, match_3}, {match_6, match_5, match_4, match_3}<>"") ,1), "NO MATCH.")
    )
  )
)

公式拆解说明 #

1. 基础变量定义 #

• student_id: 固定引用当前行的学生ID（A列）
• current_block: 统一时段格式，将秋季(F)/春季(S)时段转为全年制(B)格式，实现B1=F1=S1这类跨学期匹配
• student_age: 从学生ID提取年龄（取ID最后两位转为数字）
• student_col: 在Survey表首行匹配学生ID，定位到对应排名列的位置

2. 封装匹配逻辑（LAMBDA函数） #

get_match函数接收排名数字，完成以下操作：

• 用XLOOKUP找到该排名对应的课程名、时段、年龄范围
• 解析课程年龄范围，提取最小/最大年龄（异常情况默认0-99）
• 同时满足时段匹配+年龄符合条件时，返回"排名: 课程名"，否则返回空值

3. 按优先级匹配 #

依次检查排名6→5→4→3的匹配结果，筛选出第一个非空结果作为最终返回值；若所有排名都无匹配，返回NO MATCH.

4. 异常处理 #

如果Survey表中找不到对应学生ID，返回NO STUDENT

可选调整 #

• 无需年龄过滤时，删除student_age变量，同时去掉get_match中年龄判断的两个条件
• 若后续扩展时段规则（如B3对应F3/S3），当前的REGEXREPLACE逻辑可自动兼容

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