# Lookup based on two reference and then FX conversion

#### sypherecho

##### New Member
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>

With Regards,
Lorens Garcia Edic

Last edited:

#### oldbrewer

##### Board Regular
 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

##### Active Member
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

##### Active Member
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