如何实现非空列下INDEX(MATCH)/Lookup的自动填充？

针对你需要在Table2新增行时自动从Table1匹配对应Revenue值的需求，以下是两种可行的公式方案：

方案1：使用SUMPRODUCT + ArrayFormula #

在Table2的C2单元格输入以下公式：

=ArrayFormula(IF(A2:A="", "", SUMPRODUCT((TABLE1!A2:A=A2:A)*(TABLE1!B2:B=C$1)*(TABLE1!C1:1=B2:B)*TABLE1!C2:D)))

原理说明 #

原公式仅返回第一个匹配结果，是因为MATCH未针对每一行的条件进行数组迭代。SUMPRODUCT可以同时校验三个匹配条件：

• TABLE1!A2:A=A2:A：匹配国家
• TABLE1!B2:B=C$1：匹配PnL行（固定为C1的"Revenue"）
• TABLE1!C1:1=B2:B：匹配日期
  由于每个组合仅存在一个匹配项，SUMPRODUCT会直接返回对应单元格的数值，配合ArrayFormula实现新增行自动填充。

方案2：使用BYROW + INDEX/MATCH + ArrayFormula #

如果需要支持文本类型的返回结果，可使用此方案：

=ArrayFormula(IF(A2:A="", "", BYROW(A2:B, LAMBDA(row, INDEX(TABLE1!C2:D, MATCH(1, (TABLE1!A2:A=INDEX(row,1))*(TABLE1!B2:B=C$1)*(TABLE1!C1:1=INDEX(row,2)), 0)))))

原理说明 #

BYROW会遍历Table2的A2:B区域每一行，通过LAMBDA提取当前行的国家和日期，再用MATCH精准定位Table1中的对应行，最后用INDEX取出数值。此方式逻辑更直观，兼容文本和数值结果。

效果验证 #

当Table2新增行Country B和1/2/2022时，上述公式会自动匹配Table1中对应数据，返回7。

内容的提问来源于stack exchange，提问作者Manh Cuong Duong