Add in a column which runs a formula from another sheet

[Yp23]

Hi

I have two different excel spreadsheets and was wondering how to gather information on one tab from another.

What I want to do

• I want to add a column to data set 1 after the payment amount which has a formula which links in with Data Set 2
• Essentially if the payment currency is EUR in data set 1, I want an additional column next to the payment amount which equals to the payment amount amount multiplied by the EUR to ILS FX monthly average rate in data set 2. 
• Example for the first line item in data set 2, the amount in this new column would be equal to 3,424.14 * 3.7932289163

Current workflow (Data set 2 is on top, Data set 1 is at the bottom)

Data Set 1

Data Set 2

Appreciate any help!

[AndrewSu · Accepted solution]

@Yp23 , i believe you can just use the Join tool to join by the currency and once joined, you can use a formula tool to calculate the new value.  See the attached workflow. 

If this resolves your issue, please mark this reply as the solution so that others in the Community can benefit from our collaboration. 

Thanks!