0
votes

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.

Desired output: Col D

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?

1
Can you provide lookup table currency conversion for this? It includes all the currencies you need - Dang D. Khanh

1 Answers

1
votes

It depends on how you have your conversion values. If you have them in a table like in the below snapshot, then you can use INDEX/MATCH like this:

=INDEX($H$2:$K$5,MATCH(A2,$G$2:$G$5,0),MATCH(C2,$H$1:$K$1,0))*B2

enter image description here


Conversely if you have something more like the following snapshot, then using SUMIFS would be easier:

=SUMIFS(I:I,G:G,A2,H:H,C2)*B2

enter image description here