find value by column name

[DonLV]

Hi to all. 

https://ibb.co/MncrfsR 

Can you help me with this expressions?

Sheet 1 - currency, column 1 - [date], column 2 -[ currency 1], column 3 - [currency 2], column 4 - [currency 3]

Sheet 2, column1 - [sheet 2 ID], column2 - [date], column 3 - [currency name], column 4 - [actual rate]

the expression should find the value of the current exchange rate from the currency sheet

[TeeSee1]

Try

SWITCH([currency name],
 "currency 1", SELECT(Currency[currency 1],[Data] = [_THISROW].[data]),
 "currency 2", SELECT(Currency[currency 2],[Data] = [_THISROW].[data]),
 "currency 3", SELECT(Currency[currency 3],[Data] = [_THISROW].[data]),
 "whatever default you want.."
)

[DonLV]

@TeeSee1  thanks for your quick repl👍🙏

I repeated the expression as per your hint and came across the following error :

"SWITCH function is used incorrectly: Inputs to SWITCH() must be an initial expression, one or more value-result pairs, and a default result"

[TeeSee1]

I suppose SWITCH expects a single value as its expected results.

SELECT returns a list so try INDEX(SELECT(...), 1) for each of the select expressions.

[TeeSee1]

Additional note,

As its default value, you may need to use a decimal value like 0.0 if your currency fields use Price type.

https://support.google.com/appsheet/answer/10107700