How to MATCH currencies then perform calculation based on currency?

[consulttk]

Hoping someone can help! I have transactions in different currencies and am trying to set up a formula to calculate the USD equivalent. Basically I will have a foreign currency and amount in 2 columns and am trying to create a formula to match the currency with exchange rates listed in another column. Then also set up a calculation rule so for certain currencies it will multiply and for other currencies it will divide. I'm attaching an example of what I'm trying to do, but have no idea where to begin for the formula.

Thanks in advance!
Book7.xlsx

[oeldere]

VLookup will do that for you if you change the table.

See the attached file.

[consulttk]

VLookup will do that for you if you change the table.

See the attached file.

The only issue is the exchange rates will be pulling from another source and the order of the rates may change, so I don't know if I can have another column for the reciprocal without knowing which currencies I need the reciprocal for. Is there a way to build in an IF statement so if the currency is USD,EUR, AUD or GBP then multiply, otherwise divide?

[oeldere]

What's another source?

another worksheet / another workbook / another external link?

[consulttk]

It could be manual, copy/paste, or linked from another workbook (the source may change daily). The only consistency is how they are presented (so ZAR would always be ZAR/USD or 8.4 in my example, never USD/ZAR or .12).

[oeldere]

If you copy / paste

maybe if we change the data, so you can copy / paste them.

See the attached file.

[consulttk]

Actually I think I got it to work now using a nested IF statement, it's a little long but it seems to do the trick (attached). Thanks for the help!!

Book7.xlsx

[oeldere]

I used an table to look in, to avoid an combined if function.

See in my second file, in column G for the new data.

If you change the data in column F, the data in column G will automatic change.

Therefor the data is more flexible.

I advice you to take a look a my example, and try to understand why i made it this way.

If you have questions or comments on this item just ask or comment.