Join excel files, create new calculated column

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

Table 2 Example

Desired Table

Datasets

Accepted answers

Does the attached solution work?

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

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]?