使用Array Formula+VLookup多列匹配学生ID返回日期时结果异常的问题

我有两张学生数据表：

• BUILDING SHEET：供楼宇工作人员录入数据，D列为作为搜索键的学生ID，需在表末列填充「DISTRICT SHEET」中的日期数据。
• DISTRICT SHEET：存储表单回复，包含5个可能存在学生ID的列（R、AD、AO、AZ、BY），目标日期列为CJ列。

我尝试用嵌套VLOOKUP+ARRAYFORMULA实现需求：用BUILDING SHEET的学生ID依次搜索DISTRICT SHEET的5个列，匹配后返回对应行的CJ列日期，但结果异常——同一行的4个学生ID（对应DISTRICT SHEET的R、AD、AO、AZ列）仅2行返回正确日期8/28/23，1行空白，1行返回其他行日期。

之前尝试的两种公式：

1. 直接整合IMPORTRANGE的公式：

=arrayformula(iferror(vlookup($D$2:$D,importrange("doc-ID-string","Form Responses 1!R2:CJ"),71),iferror(vlookup($D$2:$D,importrange("doc-ID-string","Form Responses 1!AD2:CJ"),59),iferror(vlookup($D$2:$D,importrange("doc-ID-string","Form Responses 1!AO2:CJ"),48),iferror(vlookup($D$2:$D,importrange("doc-ID-string","Form Responses 1!AZ2:CJ"),37),iferror(vlookup($D$2:$D,importrange("doc-ID-string","Form Responses 1!BY2:CJ"),12),""))))))

2. 先导入数据到District Data标签页再匹配的公式：

=arrayformula(iferror(vlookup($D$2:$D,'District Data'!$R$2:$CJ,71),iferror(vlookup($D$2:$D,'District Data'!$AD$2:$CJ,59),iferror(vlookup($D$2:$D,'District Data'!$AO$2:$CJ,48),iferror(vlookup($D$2:$D,'District Data'!$AZ$2:$CJ,37),iferror(vlookup($D$2:$D,'District Data'!$BY$2:$CJ,12),""))))))

核心问题分析 #

之前的公式存在两个致命问题：

1. VLOOKUP未指定精确匹配参数（最后一个参数FALSE），默认近似匹配会导致ID匹配错位，尤其是当ID为数字格式时。
2. 嵌套VLOOKUP的搜索范围逻辑冗余，若某层VLOOKUP因近似匹配返回错误结果而非#N/A，会直接跳过后续搜索分支。

方案1：使用XLOOKUP（推荐，简洁高效） #

如果你的Google Sheets支持XLOOKUP（2020年后版本默认支持），可以用以下公式（假设已将DISTRICT SHEET数据导入到District Data标签页）：

=ARRAYFORMULA(
  BYROW(D2:D, LAMBDA(id,
    IF(id="", "",
      XLOOKUP(
        id,
        {'District Data'!R:R, 'District Data'!AD:AD, 'District Data'!AO:AO, 'District Data'!AZ:AZ, 'District Data'!BY:BY},
        {'District Data'!CJ:CJ, 'District Data'!CJ:CJ, 'District Data'!CJ:CJ, 'District Data'!CJ:CJ, 'District Data'!CJ:CJ},
        "",
        0
      )
    )
  ))

• 逻辑：用BYROW遍历每个学生ID，XLOOKUP在5个ID列中精确搜索，找到匹配后返回对应行的CJ列日期，0表示强制精确匹配。

方案2：使用INDEX/MATCH组合（兼容旧版） #

若无法使用XLOOKUP，可采用INDEX+MATCH的嵌套组合：

=ARRAYFORMULA(
  IF(D2:D="", "",
    IFERROR(
      INDEX('District Data'!CJ:CJ, MATCH(D2:D, 'District Data'!R:R, 0)),
      IFERROR(
        INDEX('District Data'!CJ:CJ, MATCH(D2:D, 'District Data'!AD:AD, 0)),
        IFERROR(
          INDEX('District Data'!CJ:CJ, MATCH(D2:D, 'District Data'!AO:AO, 0)),
          IFERROR(
            INDEX('District Data'!CJ:CJ, MATCH(D2:D, 'District Data'!AZ:AZ, 0)),
            IFERROR(
              INDEX('District Data'!CJ:CJ, MATCH(D2:D, 'District Data'!BY:BY, 0)),
              ""
            )
          )
        )
      )
    )
  )
)

• 逻辑：依次用MATCH在每个ID列中查找精确匹配的行号，再用INDEX返回CJ列对应行的日期，IFERROR兜底执行下一列搜索。

方案3：整合IMPORTRANGE的版本 #

如果直接使用IMPORTRANGE，可将数据导入临时数组后执行匹配：

=ARRAYFORMULA(
  BYROW(D2:D, LAMBDA(id,
    IF(id="", "",
      XLOOKUP(
        id,
        QUERY(IMPORTRANGE("doc-ID-string", "Form Responses 1!R:CJ"), "select Col1, Col30, Col41, Col52, Col67"),
        QUERY(IMPORTRANGE("doc-ID-string", "Form Responses 1!CJ:CJ"), "select Col1"),
        "",
        0
      )
    )
  ))

• 注意：首次使用IMPORTRANGE需要授权访问目标文档。

关键注意事项 #

• 确保学生ID的格式完全一致（均为文本或均为数字），避免因格式不匹配导致搜索失败。
• 所有匹配公式必须指定精确匹配参数，这是解决之前结果异常的核心。

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