How to use LAMBDA with GOOGLEFINANCE to perform currency conversions in an array?

Question

I'm trying to write a formula that checks the currency of a stock position and performs a currency conversion if it is anything other than USD. I'd like to make column F into an array but the GOOGLEFINANCE formula doesn't work with the arrayformula. So I've tried using LAMBDA functions with BYROW and MAP but without success.

• Column C lists the currency.
• Column E lists the entry price of a stock position in that currency.
• Column F uses the GOOGLEFINANCE formula to perform currency conversion and display the entry price in USD. (If the currency is pence (GBX), convert as GBP to USD then divide by 100. If the currency is USD, no conversion will be performed. For all other currencies, convert to USD.)
• Column G is my attempt to do what column F does but as an arrayformula.
• Column H is my attempt to do what column F does using the LAMBDA function with BYROW.

Yellow cells contain formula. Blue cells are manually inputted data.

I appreciate anyone who can take a look at my spreadsheet (linked below) and see what's the best solution for this. Sheet 2 is editable.

https://docs.google.com/spreadsheets/d/1JWkgH2Nf7CFI0Nh9H2-qEMRcwd8iMz7nxebuauplFiE/edit#gid=1126538379

Thanks! J

Answer 1

added formula to your sheet:

=MAP(C2:C,E2:E,LAMBDA(cx,dx,IF(cx="",,ROUND(IFS(cx="GBX",(GOOGLEFINANCE("GBPUSD")*dx)/100,cx="USD",dx,LEN(cx),GOOGLEFINANCE(cx&"USD")*dx),2))))

-