VLOOKUP/LOOKUP currency symbol

[daveyc3000]

I have a column where

if [Currency] = "U$" THEN [Mkt Value]*1.2727ELSE [Mkt Value] ENDIF

currently I have hardcoded the rate, but my fx rates source file is a separate file that's updated daily.

would it be possible, in the file's current format, to get the rate that's beside "CLOSE" (that's the FX rate I use daily) by doing some sort of lookup without me looking at the file then hardcoding the number?

*****

also, for month-end, i would need to do a similar lookup with not only U$, but other currencies like EM, BP, etc (those rates are in the second sheet, "ME").  i have provided one example in sheet3....would this be doable in Alteryx?

[Yoshiro_Fujimori · Accepted solution]

Hi @daveyc3000 ,

If the position of the cell for exchange rate is fixed, you can simply pick up the row and the column to get the value.

And you can use Join tool to get a corresponding rate from a rate table.

Here is a sample workflow. I hope it helps. Good luck.

Workflow

[daveyc3000]

excellent thank you so much!

[daveyc3000]

Actually, sorry, for the top workfllow....I'm also trying to integrate that rate to another file  (attached) , where the rate is represented by the symbol U$  in the "Currency" column.

the issue I'm encounter when I try to join the file is that after joining the two files, I'm only getting the "U$" results and not the rows with blanks in the currency column.

they are blank because they're our native currency and thus no conversion is required.

[Yoshiro_Fujimori]

I guess you may want to add a row for C$ (or your home currency) in your conversion table with exchange rate 1.0.

Currency	Rate
A$	0.91888
…	…
C$	1
…	…
U$	1.26455