Google Sheets中ArrayFormula结合XLOOKUP与IMPORTRANGE列数据缺失问题

问题根源 #

在ArrayFormula里嵌套XLOOKUP并指定多列返回范围时，XLOOKUP输出的二维数组会被ArrayFormula按行维度解析，仅返回第一列数据——这是因为两者的数组处理逻辑不兼容，ArrayFormula不会自动将XLOOKUP的多列结果映射到工作表的对应列上。

修复方法 #

方法1：用BYROW遍历实现自动扩展（推荐，适配跨表场景） #

通过BYROW对B4:B的每一行单独执行XLOOKUP，确保多列结果完整返回，同时配合ArrayFormula实现自动填充：

=ArrayFormula(BYROW(B4:B, LAMBDA(x, IF(x="",, XLOOKUP(x, IMPORTRANGE($G$2, "Gift Aid List!H:H"), IMPORTRANGE($G$2, "Gift Aid List!C:F"), "No GA", 0)))))

方法2：拆分列单独写公式（备选，不够简洁） #

如果不想用BYROW，可以给每列单独配置XLOOKUP公式，比如：

• G列：=ArrayFormula(XLOOKUP(B4:B, IMPORTRANGE($G$2, "Gift Aid List!H:H"), IMPORTRANGE($G$2, "Gift Aid List!C:C"), "No GA", 0))
• H列：=ArrayFormula(XLOOKUP(B4:B, IMPORTRANGE($G$2, "Gift Aid List!H:H"), IMPORTRANGE($G$2, "Gift Aid List!D:D"), "No GA", 0))
• 以此类推到对应会员表F列的工作表列，这种方法能解决问题但缺少简洁性。

同表匹配场景的简化公式 #

如果是同表内匹配（比如示例中移除IMPORTRANGE的场景），公式可简化为：

=ArrayFormula(BYROW(B4:B, LAMBDA(x, IF(x="",, XLOOKUP(x, 'GIFTAIDLIST'!H:H, 'GIFTAIDLIST'!C:F, "No GA", 0)))))

效果验证 #

上述公式会自动随B4:B新增的银行数据扩展结果，匹配成功时完整返回C-F列的会员信息，无匹配则显示“No GA”。

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