Example illustrated below, but I essentially have 3 columns:
Column A is 3-letter currency abbreviation (total 4 types).
Column B is an amount.
Column C is a 2-letter country code (total 2 types).
What I need is for column D to apply a custom conversion to the column B amount, with output in the currency of column C and based on the conversion rate of column A.
To illustrate:
Row 1: Both currency and country are same = no change to value.
Row 2: Both currency and country are same = no change to value.
Row 3: Currency is JPY, country is UK = B4 amount * JPY to GBP rate.
Row 4: Currency is EUR, country is US = B5 amount * EUR to USD rate.
So, basically an if statement to check what the code is in column A, then apply a custom multiplier based on column C.
I don't suppose anyone can think of an elegant way to do this?