如何为XLOOKUP应用ARRAY FORMULA？公式重复值问题排查

问题原因 #

嵌套ARRAYFORMULA后仅返回首个重复值，核心问题是查找键未生成对应的二维数组：

• 原公式中的$A2（行相对引用）和B$1（列相对引用），直接放入ARRAYFORMULA时不会自动扩展为行×列的配对组合，只会以左上角第一个单元格的A2&B1作为唯一查找键，导致XLOOKUP仅返回一次匹配结果并重复填充。
• 额外嵌套的ARRAYFORMULA('Unit Update'!$B$2:$B & 'Unit Update'!$D$2:$D)是冗余操作，&运算符本身可在ARRAYFORMULA环境中自动执行数组拼接，无需重复嵌套。

最优实现方法 #

方法1：ARRAYFORMULA+二维查找键（高效简洁） #

=ARRAYFORMULA(
  IF(
    $A$2:$A="", "",
    IFNA(
      XLOOKUP(
        $A$2:$A & TRANSPOSE(B$1:Z$1),
        'Unit Update'!$B$2:$B & 'Unit Update'!$D$2:$D,
        'Unit Update'!$A$2:$A,
        ""
      ),
      ""
    )
  )
)

• 原理：$A$2:$A & TRANSPOSE(B$1:Z$1)将A列所有值与表头行（B1到Z1）的每个值逐个拼接，生成与目标区域尺寸一致的二维查找键数组，XLOOKUP会对每个键独立匹配，返回完整的二维结果数组。
• 注意：需根据实际表头范围调整B$1:Z$1，确保覆盖所有需要匹配的列。

方法2：BYROW+BYCOL逐单元格处理（直观易调试） #

=BYROW($A$2:$A, LAMBDA(row_val,
  IF(row_val="",,
    BYCOL(B$1:Z$1, LAMBDA(col_val,
      IFNA(XLOOKUP(row_val&col_val, 'Unit Update'!$B:$B&'Unit Update'!$D:$D, 'Unit Update'!$A:$A, ""), "")
    ))
  )
))

• 原理：外层BYROW遍历A列每一行的值，内层BYCOL遍历表头每一列的值，逐单元格生成查找键并执行XLOOKUP，自动将结果扩展至整个区域。
• 优势：逻辑清晰，调试时可单独查看某一行/列的运算结果，适配复杂匹配场景。

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