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

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

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

1,081,773
Messages
5,361,207
Members
400,617
Latest member
barron1

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...