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 1
CF General	CF Specific	currency	1/1/16	1/2/16
Client	Customer A	Sing$	100	500
Client	Customer B	USD	200	400
Client	Customer C	MUR	300	300
Client	Customer D	Sing$	300	300
Vendor	Customer E	Sing$	500	200
Vendor	Customer F	USD	500	100

<tbody>
</tbody>

Table B: FX Reference Table
currency	USD
SGD	1.4402
USD	1
MUR	36.88

<tbody>
</tbody>

Table C: Values should be in USD (Final Result)

CF General	2016-01-01	2016-01-01
Client	11,640.08	12,216.16
Vendor	1,076.08	388.04

<tbody>
</tbody>

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 1
CF General	CF Specific	currency	01/01/2016	02/01/2016	total	total usd
Client	Customer A	Sing$	100	500	600	864.12			144.02	720.1
Client	Customer B	USD	200	400	600	600			200	400
Client	Customer C	MUR	300	300	600	22128			11064	11064
Client	Customer D	Sing$	300	300	600	864.12			432.06	432.06
Vendor	Customer E	Sing$	500	200	700	1008.14			720.1	288.04
Vendor	Customer F	USD	500	100	600	600			500	100
Table B: FX Reference Table
currency	USD
MUR	36.88
SGD	1.4402
USD	1
										01/01/2016	02/01/2016
Table C: Values should be in USD (Final Result)						client	11840.08	12616.16
CF General	01/01/2016	01/01/2016							vendor	1220.1	388.04
Client	11,640.08	12,216.16
Vendor	1,076.08	388.04
									my figures do not agree with yours
									I used helper columns for conversion
									then sum product

<colgroup><col><col><col><col><col><col span="5"><col span="2"><col span="2"></colgroup><tbody>
</tbody>

 

[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.

	A	B	C	D	E	F	G	H
1	CF General	CF Specific	Curr.	01/01/16	01/02/16		Curr.	USD
2	Client	Cust A	Sing$	100	500		Sing$	1,4402
3	Client	Cust B	USD	200	400		USD	1
4	Client	Cust C	MUR	300	300		MUR	36,88
5	Client	Cust D	Sing$	300	300
6	Vendor	Cust E	Sing$	500	200
7	Vendor	Cust F	USD	500	100
8
9	CF GENERAL	01/01/2016	01/02/2016
10	Client	11 840,08	12 616,16
11	Vendor	1 220,10	388,04

<tbody>
</tbody>

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