在数组公式中通过二维查找返回关联列标题的问题

问题根源 #

原公式中HLOOKUP与SEARCH的组合无法在数组环境下逐行处理B列的每个令牌，导致始终返回首个列标题，且数组兼容性不足。

修正后的公式（新版Google Sheets） #

=ARRAYFORMULA(
  BYROW(B2:B, LAMBDA(token,
    IFERROR(
      IFS(
        ISBLANK(token), "",
        COUNTIF(D2:Z1000, token) = 0, "Invalid",
        COUNTIF(B2:B, token) > 1, "Duplicate",
        TRUE, INDEX(D1:Z1, MATCH(TRUE, ISNUMBER(SEARCH(token, D2:Z1000)), 0))
      ),
      "Invalid"
    )
  ))
)

公式细节说明 #

• BYROW + LAMBDA：逐行遍历B2:B的每个令牌，确保每个令牌独立执行匹配逻辑，解决原数组公式的批量处理缺陷。
• ISNUMBER(SEARCH(token, D2:Z1000))：生成布尔数组，标记当前令牌在目标区域D2:Z1000中的出现位置。
• MATCH(TRUE, ..., 0)：定位首个包含令牌的列，再通过INDEX(D1:Z1, ...)提取对应列标题。
• 完整保留原边缘逻辑：
  • 空令牌返回空字符串
  • 令牌未在目标区域存在返回Invalid
  • B列令牌重复返回Duplicate

兼容旧版Google Sheets的替代公式 #

若你的版本不支持BYROW和LAMBDA，可使用基于矩阵运算的方案：

=ARRAYFORMULA(
  IFERROR(
    IFS(
      ISBLANK(B2:B), "",
      COUNTIF(D2:Z1000, B2:B) = 0, "Invalid",
      COUNTIF(B2:B, B2:B) > 1, "Duplicate",
      TRUE, INDEX(D1:Z1, 1, MMULT(--ISNUMBER(SEARCH(B2:B, TRANSPOSE(D2:Z1000))), ROW(D2:D1000)^0))
    ),
    "Invalid"
  )
)

替代方案说明 #

• TRANSPOSE(D2:Z1000)：将目标区域转置为行结构，适配B列令牌的批量匹配需求。
• MMULT(...)：通过矩阵乘法计算每个令牌对应的列索引，实现纯数组化的位置匹配。

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