|
求助各位大神,现在想从表1中提取F和E列数据到表2,前提是要满足两个条件(表1中型号相同且数量大于表2中相同型号的数量),满足这两个条件把表1中E列数据提取到对应的表2中来。
表1
合同号 | 序号 | 型号 | 数量 | 合同号 | 序号 | 201632532011 | 1 | DCM 48M035J010 | 14660 | 201632532011 | 1 | 201632532011 | 2 | DCM 48M404J180 | 65692 | 201632532011 | 2 | 201632532011 | 3 | DCM 48M308F080 | 60068 | 201632532011 | 3 | 201632532011 | 4 | DCM 42M266J220 | 32480 | 201632532011 | 4 | 201632532011 | 5 | DCM 48M266J120 | 23672 | 201632532011 | 5 | 201632532011 | 6 | DCM 48M843D010Z | 192200 | 201632532011 | 6 | 201632532011 | 7 | DCM 24H404F210 G | 13040 | 201632532011 | 7 | 201632532011 | 8 | DCM 42M843K080 | 33600 | 201632532011 | 8 | 201632532011 | 9 | DCM 42M843K090 | 39850 | 201632532011 | 9 | 201632532011 | 10 | DCM 24C293K130(HF) G | 342693 | 201632532011 | 10 | 201632532011 | 11 | DCM 48M060J042 | 11600 | 201632532011 | 11 | 201632532012 | 1 | DCM 48M035J010 | 14660 | 201632532012 | 1 |
表2型号 | 数量 | 提取数据 | | DCM 24H404F210 G | 3000 | 0 | 14 | DCM 42M843K080 | 4000 | 0 | 16 | DCM 42M843K090 | 5000 | 0 | 18 | DCM 24C293K130(HF) G | 6000 | 0 | 20 | DCM 48M060J042 | 12000 | 0 | 0 |
- =IFERROR(INDEX('1'!$E$1:$E$23,MATCH(1,(('1'!$C$1:$C$23=A2)*('1'!$D$1:$D$23>B2)),)),"")
复制代码数组下拉
|
|