Excel基于双列条件匹配提取Mater类对应成本的公式咨询

原公式错误原因 #

你写的=IF(AND(Table2!A1=Table1!$A$1:$A$6,Table1$C$1:$C$6="Mater"),Table1!$D$2:$D$6,"")核心问题是AND函数会将整组数组判断合并为单个布尔结果，不支持逐行匹配对应条目，无法返回目标行的成本数值。

可用实现方案 #

以下方案均默认表2从第2行开始为数据行，A列为合同号，需在B列（Mater Cost）填入公式，向下拖拽填充即可。

方案1：INDEX+MATCH（全Excel版本兼容） #

兼容性最强，所有Excel版本均支持：

=IFERROR(INDEX(Table1!$D:$D,MATCH(1,(Table1!$A:$A=A2)*(Table1!$C:$C="Mater"),0)),"")

• 逻辑说明：两个匹配条件相乘，同时满足时结果为1，MATCH函数定位第一个符合条件的行号，INDEX提取对应行的成本数值，IFERROR处理无匹配的场景返回空值。
• 注意：Excel 2019及更早版本输入公式后，需要按Ctrl+Shift+Enter组合键触发数组计算才能生效，Excel 365/2021直接回车即可。

方案2：SUMIFS（无重复条目时最优） #

如果同一个合同号下Type="Mater"的条目唯一，用这个公式最简便：

=SUMIFS(Table1!$D:$D,Table1!$A:$A,A2,Table1!$C:$C,"Mater")

• 逻辑说明：多条件求和，对同时满足「合同号匹配」「类型为Mater」的成本字段求和，单条目时求和结果就是目标成本值，无匹配时返回0，如果需要无匹配返回空值可以套一层IF：=IF(SUMIFS(Table1!$D:$D,Table1!$A:$A,A2,Table1!$C:$C="Mater")=0,"",SUMIFS(Table1!$D:$D,Table1!$A:$A,A2,Table1!$C:$C="Mater"))

方案3：XLOOKUP（Excel 365/2021专属） #

新版本Excel可直接用更简洁的XLOOKUP实现：

=XLOOKUP(A2&"Mater",Table1!$A:$A&Table1!$C:$C,Table1!$D:$D,"")

• 逻辑说明：将两个条件拼接为查找值，对应拼接表1的合同号和类型列作为查找区域，直接返回匹配到的成本，无匹配时返回空值。

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