I have built a spreadsheet to help me keep track of my spending during my holidays. Whichever currency I buy in, it converts the raw amount into my local currency (Malaysian Ringgit MYR). Here is how it looks:
I am using a combination of INDEX and GOOGLEFINANCE for this. Here is the exact code used inside cell E3:
When copying down, it seems the INDEX-function is inconsistent and throws random #REF! errors with the following error message:
Error Function INDEX parameter 2 value is 2. Valid values are between 0 and 1 inclusive.
The thing is that there is a row 2 and column 2 for the googlefinance currency list that is referenced. I can't figure out why it is not working properly. If I reload and/or change some figures, it randomly works on a few rows. I would like a rock-solid formula that works at all times. An ideas on how to fix this?
Here is a link to a demo-sheet that is publicly editable. Play around to your hearts content! https://docs.google.com/spreadsheets/d/1SaMEfu-CpkA-m8S0V68ID9Xn5KD5Hdfk3s2vrGkhiNs/edit?usp=sharing