Variable GoogleFinance formula (Google Spreadsheets)

Question

I have a question regarding a formula: =GoogleFinance("CURRENCY:(A2)EUR")*B2

The content of cell A2 = USD, the content of cell B2 = 60, so the formula should be translated as =GoogleFinance("CURRENCY:USDEUR")*60. But it doesn't work like that unfortunately.

I'm getting #N/Ainstead of how much Euro you get for 60 USD. The B2 works fine, that's not the problem. But the formula seems to have trouble with the use of (A2) instead of USD.

Why doesn't this work? And does anyone think: why would you want that? This is a much easier solution. FYI: the database I'm making will have a lot different countries and currencies, that's why I want to keep the formula variable.

Answer 1

Short answer

Use

=GOOGLEFINANCE("CURRENCY:"&A2&"EUR")*B2

Explanation

Google Sheets have three ways for concatenating strings.

1. CONCATENATE() function
2. CONCAT() function
3. & operator

Instead of "CURRENCY:(A2)EUR", use

1. CONCATENATE("CURRENCY:",A2,"EUR")
2. CONCAT("CURRENCY:",CONCAT(A2,"EUR"))
3. "CURRENCY:"&A2&"EUR"