Muliply values with vlookup?

[Pojzon]

[TABLE="width: 656"]
<tbody>[TR]
[TD]Help table[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD]Result table[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Name1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]Name2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]15[/TD]
[TD][/TD]
[TD]Name3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name4[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]Name4[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Multiplied sum[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]40[/TD]
[/TR]
</tbody>[/TABLE]

Hello,
I need to multiply values from help table with result table. In final file there would be around 50 names. I tried using vlookup, but with no luck. Is there any simple way that I could automate this? Thank you for any help.

 

[DRSteele]

Welcome to the forum.

Summing arrays multiplied together is an easier way to extract the conditional values.

Copy E6 across.

	A	B	C	D	E	F	G	H	I	J	K	L
Name1	Name1
Name2	Name2
Name3	Name3
Name4	Name4
Multiplied sum

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]Help table[/TD]
[TD="bgcolor: #FCE4D6"]Amount[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]Result table[/TD]
[TD="bgcolor: #FFF2CC, align: right"]1[/TD]
[TD="bgcolor: #FFF2CC, align: right"]2[/TD]
[TD="bgcolor: #FFF2CC, align: right"]3[/TD]
[TD="bgcolor: #FFF2CC, align: right"]4[/TD]
[TD="bgcolor: #FFF2CC, align: right"]5[/TD]
[TD="bgcolor: #FFF2CC, align: right"]6[/TD]
[TD="bgcolor: #FFF2CC, align: right"]7[/TD]
[TD="bgcolor: #FFF2CC, align: right"]8[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #E2EFDA, align: right"]50[/TD]
[TD="bgcolor: #E2EFDA, align: right"]100[/TD]
[TD="bgcolor: #E2EFDA, align: right"]60[/TD]
[TD="bgcolor: #E2EFDA, align: right"]50[/TD]
[TD="bgcolor: #E2EFDA, align: right"]125[/TD]
[TD="bgcolor: #E2EFDA, align: right"]60[/TD]
[TD="bgcolor: #E2EFDA, align: right"]60[/TD]
[TD="bgcolor: #E2EFDA, align: right"]40[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E6[/TH]
[TD="align: left"]=SUMPRODUCT($B$2:$B$5*E2:E5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


<strike></strike>

 

[Pojzon]

Thank you very much for answer, but I've got another question. I believe this method will only work as long as order of names in help table is always the same as order in result table. Is there anyway to make it work with different order?

 

[DRSteele]

Yes, there is. Simply include another array.

This goes in E6.

=SUMPRODUCT(($D$2:$D$5=$A$2:$A$5)*$B$2:$B$5*E2:E5)