Selecting a column to look up a value from based on value on another column

I have a set of data with transactions with various currencies, I need to look up the daily exchange rate from a list that is set up as below selecting the column based on the currency for each transaction.

At the moment my workflow is set up to filter by each currency, look up the value for each flow with a Join tool then combine the data again (screenshot below), which looks pretty redundant and would become particularly messy once more currencies were added.

Any ideas on what would be the best way to simplify this?

Thanks

Reporting

Preparation

Accepted answers

atcodedog05

Hi @Kvvzz

Here is how we can do it.

Workflow:

Hope this helps : )

Rates cur.yxmd

All comments

atcodedog05

Hi @Kvvzz

You can use tranpose tool to convert all columns of currencies to rows. This way you can also join on currency for the respective. If you provide us some sample data we can take a look into it.

Kvvzz

Thanks @atcodedog05, I thought using Transpose tool would be the way to go but not sure how to then look up the value based on the date? I've attached an example of the sort of data I would have and the exchange rate filed.

FX.xlsx

Data.xlsx

apathetichell

kind of like this?

currency transpose.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703