How to combine two table information?

[EduPAz]

Hi,

Can someone please help me with this:

I have a table with these columns:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product
[/TD]
[TD]Original currency
[/TD]
[TD]Jan-16
[/TD]
[TD]Feb-16
[/TD]
[TD]...
[/TD]
[TD]Dec-16
[/TD]
[TD]Total 2016 in original currency
[/TD]
[TD]Total 2016 in CAD
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]USD
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]8
[/TD]
[TD]XX
[/TD]
[TD](3*1.3)+(5*1.28)+...+(8*1.294)
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]EUR
[/TD]
[TD]3.4
[/TD]
[TD]3.2
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]XXX
[/TD]
[TD](3.4*1.4)+(3.2*1.42)+(1*1.5)
[/TD]
[/TR]
</tbody>[/TABLE]

I have another table with the monthly exchanges rates:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan-16
[/TD]
[TD]Feb-16
[/TD]
[TD]...
[/TD]
[TD]Dec-16
[/TD]
[/TR]
[TR]
[TD]USD
[/TD]
[TD]1.3
[/TD]
[TD]1.28
[/TD]
[TD][/TD]
[TD]1.294
[/TD]
[/TR]
[TR]
[TD]EUR
[/TD]
[TD]1.4
[/TD]
[TD]1.42
[/TD]
[TD][/TD]
[TD]1.5
[/TD]
[/TR]
[TR]
[TD]CAD
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

How can I get in an efficient way the Total 2016 in CAD?

Thanks,

Edu

 

[JoeMo]

You need to extend the ranges in the formula below to cover all 12 months. Here B9:E9 hold s the exchange rates for US to CAD.
Sheet10

	A	B	C	D	E	F	G	H
Product	Original currency	Total 2016 in original currency	Total 2016 in CAD
A	USD		XX

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:41px;"><col style="width:113px;"><col style="width:49px;"><col style="width:49px;"><col style="width:49px;"><col style="width:49px;"><col style="width:84px;"><col style="width:114px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]16-Jan[/TD]
[TD="align: right"]16-Feb[/TD]
[TD="align: right"]16-Mar[/TD]
[TD="align: right"]16-Dec[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

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

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

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

</tbody>

Spreadsheet Formulas

Cell Formula H2 =SUMPRODUCT($C$2:$F$2,$B$9:$E$9) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[EduPAz]

Hi,

Thank you for your answer.

The formula works for USD (column B). How could the EUR be incorporated into the formula?

Thanks,

Edu

 

[JoeMo]

Hi,

Thank you for your answer.

The formula works for USD (column B). How could the EUR be incorporated into the formula?

Thanks,

Edu

You are welcome - thanks for the reply.

For EUR use same formula but change the ranges to adhere to the position of the raw data in the two tables.

 

[Peter_SSs]

How could the EUR be incorporated into the formula?

Like this

<b>Total Currency</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:119px;" /><col style="width:59px;" /><col style="width:59px;" /><col style="width:60px;" /><col style="width:60px;" /><col style="width:124px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >H</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Product</td><td style="font-size:10pt; ">Original currency</td><td style="font-size:10pt; text-align:right; ">Jan-16</td><td style="font-size:10pt; text-align:right; ">Feb-16</td><td style="font-size:10pt; ">...</td><td style="font-size:10pt; text-align:right; ">Dec-16</td><td style="font-size:10pt; text-align:right; ">Total 2016 in CAD</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">A</td><td style="font-size:10pt; ">USD</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">8</td><td style="font-size:10pt; text-align:right; ">20.652</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">C</td><td style="font-size:10pt; ">EUR</td><td style="font-size:10pt; text-align:right; ">3.4</td><td style="font-size:10pt; text-align:right; ">3.2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">10.804</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Jan-16</td><td style="font-size:10pt; text-align:right; ">Feb-16</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Dec-16</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; ">USD</td><td style="font-size:10pt; text-align:right; ">1.3</td><td style="font-size:10pt; text-align:right; ">1.28</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1.294</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; ">EUR</td><td style="font-size:10pt; text-align:right; ">1.4</td><td style="font-size:10pt; text-align:right; ">1.42</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1.5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; ">CAD</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H2</td><td >=SUMPRODUCT($C2:F2,INDEX<span style=' color:008000; '>(B$9:E$11,MATCH<span style=' color:#0000ff; '>(B2,A$9:A$11,0)</span>,0)</span>)</td></tr></table></td></tr></table>