如何基于另一列表返回值列表并实现SUMPRODUCT计算

方法1：Excel 365/2021 动态数组公式 #

利用XLOOKUP的批量匹配能力直接生成Cost数组，再结合SUMPRODUCT完成计算：

=SUMPRODUCT(XLOOKUP(表1!A1:E1, 表2!$A$2:$A$6, 表2!$B$2:$B$6), 表1!A2:E2)

• XLOOKUP会根据表1的表头（A、B、C、E、C），批量匹配表2的Type列，返回对应的Cost数组{34,10,2,5,9}
• 后续SUMPRODUCT自动将该数组与表1的行值数组{1,0,0,1,0}对应相乘后求和，直接得到结果39

方法2：兼容旧版本Excel的SUMPRODUCT+INDEX/MATCH组合 #

如果使用不支持动态数组的Excel版本，可通过数组形式的INDEX+MATCH实现批量匹配：

=SUMPRODUCT(表1!A2:E2, INDEX(表2!$B$2:$B$6, MATCH(表1!A1:E1, 表2!$A$2:$A$6, 0)))

• MATCH先返回表1每个表头在表2Type列的位置，INDEX根据这些位置提取对应的Cost值，生成目标数组
• 最后通过SUMPRODUCT完成乘积求和，无需手动逐个设置VLOOKUP

方法3：直接用SUMPRODUCT多条件匹配（跳过中间列表） #

无需生成Cost数组，直接通过多条件判断完成匹配与求和：

=SUMPRODUCT(表1!A2:E2, 表2!$B$2:$B$6*(表2!$A$2:$A$6=TRANSPOSE(表1!A1:E1)))

• TRANSPOSE(表1!A1:E1)将横向表头转为纵向，与表2Type列对比生成布尔匹配数组
• 布尔数组与Cost列相乘后，得到对应表头的Cost值，再与表1行值相乘求和
• 注意：旧版本Excel输入后需按Ctrl+Shift+Enter以数组公式执行

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