Lookup based on two reference and then FX conversion

[sypherecho]

To whom it may concern,

I am having a problem for a couple of weeks now wherein I am trying to find the Match of Table C to Table based on CF General and Dates, and then convert them to USD.
I have been using a couple of combination of formulas (Vlookup, SUMIF, Substitute) in the formula bar but I do not know how to do this.



[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CF General[/TD]
[TD]CF Specific[/TD]
[TD]currency[/TD]
[TD]1/1/16[/TD]
[TD]1/2/16[/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]Customer A[/TD]
[TD]Sing$[/TD]
[TD]100[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]Customer B[/TD]
[TD]USD[/TD]
[TD]200[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]Customer C[/TD]
[TD]MUR[/TD]
[TD]300[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]Customer D[/TD]
[TD]Sing$[/TD]
[TD]300[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Vendor[/TD]
[TD]Customer E[/TD]
[TD]Sing$[/TD]
[TD]500[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Vendor[/TD]
[TD]Customer F[/TD]
[TD]USD[/TD]
[TD]500[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Table B: FX Reference Table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]currency[/TD]
[TD="align: center"]USD[/TD]
[/TR]
[TR]
[TD]SGD[/TD]
[TD]1.4402[/TD]
[/TR]
[TR]
[TD]USD[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]MUR[/TD]
[TD]36.88[/TD]
[/TR]
</tbody>[/TABLE]


Table C: Values should be in USD (Final Result)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]CF General[/TD]
[TD="align: center"]2016-01-01[/TD]
[TD="align: center"]2016-01-01[/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD="align: right"]11,640.08[/TD]
[TD="align: right"]12,216.16[/TD]
[/TR]
[TR]
[TD]Vendor[/TD]
[TD="align: right"]1,076.08[/TD]
[TD="align: right"]388.04[/TD]
[/TR]
</tbody>[/TABLE]


You can post your answers by replying to my thread or If you know any video tutorial in YouTube that might help me, you can also give me that link by replying to my thread.

Thank you in advance


With Regards,
Lorens Garcia Edic

 

[oldbrewer]

[TABLE="width: 1061"]
<colgroup><col><col><col><col><col><col span="5"><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CF General[/TD]
[TD]CF Specific[/TD]
[TD]currency[/TD]
[TD="align: right"]01/01/2016[/TD]
[TD="align: right"]02/01/2016[/TD]
[TD]total[/TD]
[TD]total usd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]Customer A[/TD]
[TD]Sing$[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]864.12[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]144.02[/TD]
[TD="align: right"]720.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]Customer B[/TD]
[TD]USD[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]Customer C[/TD]
[TD]MUR[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]22128[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11064[/TD]
[TD="align: right"]11064[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]Customer D[/TD]
[TD]Sing$[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]864.12[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]432.06[/TD]
[TD="align: right"]432.06[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Vendor[/TD]
[TD]Customer E[/TD]
[TD]Sing$[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]1008.14[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]720.1[/TD]
[TD="align: right"]288.04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Vendor[/TD]
[TD]Customer F[/TD]
[TD]USD[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Table B: FX Reference Table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]currency[/TD]
[TD]USD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MUR[/TD]
[TD="align: right"]36.88[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SGD[/TD]
[TD="align: right"]1.4402[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]USD[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/01/2016[/TD]
[TD="align: right"]02/01/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Table C: Values should be in USD (Final Result)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]client[/TD]
[TD="align: right"]11840.08[/TD]
[TD="align: right"]12616.16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CF General[/TD]
[TD="align: right"]01/01/2016[/TD]
[TD="align: right"]01/01/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]vendor[/TD]
[TD="align: right"]1220.1[/TD]
[TD="align: right"]388.04[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD="align: right"]11,640.08[/TD]
[TD="align: right"]12,216.16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Vendor[/TD]
[TD="align: right"]1,076.08[/TD]
[TD="align: right"]388.04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]my figures do not agree with yours[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]I used helper columns for conversion[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]then sum product[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[XLearner]

Hi,

Same totals as OldBrewer + your your Currency table should contain Sing$ instead of SGD (or vice-versa) otherwise I don't see how this can work. If you have Excel 2007 or + no helper column needed. Combining SUMPRODUCT and SUMIFS does it.

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CF General
[/TD]
[TD]CF Specific
[/TD]
[TD]Curr.
[/TD]
[TD]01/01/16
[/TD]
[TD]01/02/16[/TD]
[TD][/TD]
[TD]Curr.
[/TD]
[TD]USD[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Client[/TD]
[TD]Cust A[/TD]
[TD]Sing$[/TD]
[TD]100[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]Sing$[/TD]
[TD]1,4402[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Client[/TD]
[TD]Cust B[/TD]
[TD]USD[/TD]
[TD]200[/TD]
[TD]400[/TD]
[TD][/TD]
[TD]USD[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Client[/TD]
[TD]Cust C[/TD]
[TD]MUR[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD][/TD]
[TD]MUR[/TD]
[TD]36,88[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Client[/TD]
[TD]Cust D[/TD]
[TD]Sing$[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Vendor[/TD]
[TD]Cust E[/TD]
[TD]Sing$[/TD]
[TD]500[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Vendor[/TD]
[TD]Cust F[/TD]
[TD]USD[/TD]
[TD]500[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]CF GENERAL[/TD]
[TD]01/01/2016
[/TD]
[TD]01/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Client[/TD]
[TD="align: right"]11 840,08[/TD]
[TD="align: right"]12 616,16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Vendor[/TD]
[TD="align: right"]1 220,10[/TD]
[TD="align: right"]388,04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In B10: SUMPRODUCT(SUMIFS(D$2:D$7; $A$2:$A$7;$A10; $C$2:$C$7;$G$2:$G$4); $H$2:$H$4)
copy down for Vendor. Copy right for next Month(s)

Regards
XLearner

 

[XLearner]

Hi again,

If you don't have SUMIFS the following works as well (column A MUST be sorted A-Z though). In B10:
=SUMPRODUCT( SUMIF($G$2:$G$4;$C$2:$C$7;$H$2:$H$4)* D$2:D$7; --(LOOKUP($A$2:$A$7;$A$2:$A$7)=$A10))
Copy down for Vendor. Copy right for next Month(s)

The above formula is adapted from the amazing Excel Magic Trick 1085: How To Simulate VLOOKUP Helper Column In Array Formula (Number or Text)

Regards
XLearner

 

[XLearner]

Since I can't amend my previous post.... In fact in this case Column A doesn't need to be sorted as we don't need the LOOKUP part. In B10:
=SUMPRODUCT( SUMIF($G$2:$G$4;$C$2:$C$7;$H$2:$H$4)* D$2:D$7; --($A$2:$A$7=$A10) ) does it

Apologies for the confusion
Regards
XLearner