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

[Kvvzz]

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

[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.

[atcodedog05 · Accepted solution]

Hi @Kvvzz 

Here is how we can do it.

Workflow:

Hope this helps : )

[apathetichell]

kind of like this?

[Kvvzz]

That's perfect, thanks @atcodedog05! 

[Kvvzz]

Thank you @apathetichell, yes, that also works 🙂