Question

怎么在Excel匹配两列数据

其中一列数据某几项之和等于另一项列数据的某个数据
例如A列数据1.2.3 B列数据3.4.6 结果就是1 2等于3 A列的1行2行与B列1行匹配

Answer 1

C1输入

=IF(B1=$A$1+$A$2,$A$1&"."&$A$2,IF(B2=$A$1+$A$3,$A$1&"."&$A$3,IF(B1=$A$2+$A$3,$A$2&"."&$A$3,IF(B1=$A$1+$A$2+$A$3,$A$1&"."&$A$2&"."&$A$3,"无匹配"))))

下拉

Answer 2

公式一:
=index(sheet2!c:c,match($a2&$b2,sheet2!$a$1:$a$1000&sheet2!$b$1:$b$1000,))
数组公式,按CTRL+SHIFT+ENTER结束
右拉填充,下拉填充
公式二:
=vlookup($a2&$b2,if({1,0},sheet2!$a$1:$a$1000&sheet2!$b$1:$b$1000,sheet2!c$1:c$1000),2,)
数组公式,按CTRL+SHIFT+ENTER结束
右拉填充,下拉填充
公式三:
=sumproduct((sheet2!$a$2:$a$1000=$a2)*(sheet2!$b$2:$b$1000=$b2)*sheet2!c$2:c$1000)
右拉填充,下拉填充

Answer 3

C2=SUMPRODUCT((SHEET2!A:A=A2)*(SHEET2!B:B=B2)*C:C)
D2=SUMPRODUCT((SHEET2!A:A=A2)*(SHEET2!B:B=B2)*D:D)