CODEWITHSUNDEEP
arraysgoogle-apps-scriptgoogle-sheetsgoogle-finance
Galeano
Galeano

Reputation: 21

Google Sheets Array Function over Google Finance Currencies

I'm looking to assign an array over currency conversion rates from the google finance function. I copied this formula above and adapted it to my spreadsheet, but I keep getting the following error message:

"Error: Did not find value '4/8/2020' in VLOOKUP evaluation."

'4/8/2020' is the date. I don't know enough about VLOOKUP to understand the problem.

=ARRAYFORMULA(IF(B2:B<>"", IF(G2:G=H1, C2:C, C2:C*VLOOKUP(TO_TEXT(B2:B), TO_TEXT(QUERY(GOOGLEFINANCE("CURRENCY:"&G2:G&H1, "price", MIN(B:B), MAX(B:B)+1), "offset 1 format Col1'mm/dd/yyyy'", 0)), 2, 0)),""))

I'm looking to see the expense I logged in Column C. If it is in the same currency I'm converting, then I return the original expense, otherwise I'll multiply the expense by the conversion rate to get a result.

https://docs.google.com/spreadsheets/d/1HNZvza9jR8gYk3QlVMgx2nfN4B40oDhyOYVthwQ4HzE/edit?usp=sharing

Here is a link to the spreadsheet with the formula, so it's easier to understand. I appreciate any and all help!

Best, Galeano

Upvotes: 2

Views: 642

Answers (1)

Broly
Broly

Reputation: 921

The reason your formula isn't working is because when you use TO_TEXT in the search key for VLOOKUP it converts the date to text as is, which it is then not able to find in the output of QUERY because the format is different. For example date 4/9/2020 from TO_TEXT becomes text 4/9/2020 but the QUERY will output the same date as 04/09/2020 and hence VLOOKUP throws an error.

Try this modification to your formula that is working for me.

=ARRAYFORMULA(IF(B2:B<>"", IF(G2:G=H1, C2:C, C2:C*VLOOKUP(TO_TEXT(TEXT(B2:B, "MM/DD/YYYY")), TO_TEXT(QUERY(GOOGLEFINANCE("CURRENCY:"&G2:G&H1, "price", MIN(B:B), MAX(B:B)+1), "offset 1 format Col1'mm/dd/yyyy'", 0)), 2, 0)),""))

Upvotes: 2

Related Questions