Join excel files, create new calculated column

[JJH]

I have 2 excel files.

1st file: static data. contains a list of items with cash values in multiple currencies

2nd file: connected to database. Pulls live FX rate of multiple FOREX currency pairs

I want to combine the files to create a new column called 'CAD Equivalent' for all of the cash value rows in the 1st file.

Any suggestions on how to introduce the 2nd file to be able to compute the CAD equivalent value?

Table 1 Example

Item #	Currency	Value
1	EUR	100
2	CAD	500
3	USD	2
4	CHF	5

Table 2 Example

Currency From	Currency To	FX Rate
CAD	EUR	1.22
CAD	USD	1.33
CHF	USD	1.01

Desired Table

Item #	Currency	Value	CAD Value
1	EUR	100	122
2	CAD	500	500

[AbhilashR]

Hi @JJH, which column from Table 2 should be used to join Table 1? Currency From or Currency To column? 

[JJH]

The reason I want to join table 1 &  2 is because I want to be able to create a column that shows the CAD equivalent value using the below formula:

New column name = CAD Equivalent

Formula

IF Currency = "CAD", [CAD Equivalent] = [Value]

ELSEIF Currency! = "CAD", [CAD Equivalent] = [Value] * [FX Rate]

ENDIF

How can I join the 2 tables to create the 'FX Rate' column that I can use to compute [CAD Equivalent]?

Item #	Currency	Value	FX Rate (pulled from 2nd table)	CAD Equivalent
1	EUR	100	1.22
2	CAD	500	1
3	USD	2	xxx
4	CHF	5	xxx

[AbhilashR · Accepted solution]

Does the attached solution work?

[JJH]

Yes, this is exactly what I trying to accomplish. Thank you!