2

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

Jescanellas
  • 2,555
  • 2
  • 9
  • 20
Galeano
  • 21
  • 1

1 Answers1

2

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)),""))

Broly
  • 799
  • 3
  • 16
  • Thanks a lot, Broly, that fixed it. It seems I'm having a different error now with Google Finance. I'm using the formula you provided and the first few instances with different currencies work great. Further down it no longer checks the specific currency. I get this error "When evaluating GOOGLEFINANCE, the query for the symbol: 'CURRENCY:EUREUR' returned no data." The only problem is, I wasn't using EUR in that cell. I'm actually using CHF, but it seems the currency doesn't matter. USD doesn't work either. Any ideas why this might happen further down the results and not at first ? – Galeano May 14 '20 at 19:08
  • Can you share a sample sheet where this is happening? – Broly May 15 '20 at 06:22
  • https://docs.google.com/spreadsheets/d/1HNZvza9jR8gYk3QlVMgx2nfN4B40oDhyOYVthwQ4HzE/edit?usp=sharing – Galeano May 16 '20 at 12:44
  • Row numbers 276, 284, 285 for example – Galeano May 16 '20 at 12:44
  • sorry to bother you again with this issue. Do you think you might be able to look at the link I sent when you have some time. All the best!! I have a feeling I have something in the syntax wrong again, but the idea should work in theory. – Galeano May 25 '20 at 00:39