|1||Time since start||Time difference||Result|
OK, this one has me stumped.
Column A is just values.
Formula in B3 is A3-A2, copied down.
Formula in C3 is VLOOKUP(B3,LookupTable,35,FALSE), copied down. So C4 is VLOOKUP(B4,OutputGrid,35,FALSE), etc.
So how to explain the errors? It works fine for the first 2 iterations, then fails for... no reason? I tried using "Evaluate Formula" function, but in all cases everything looks identical, except in C5 (and beyond) the last step gives a #N/A instead of 10.
- LookupTable is on another sheet. It's pretty large
- LookupTable is a named range, but the failure persists even if I use regular range references.
- If I use VLOOKUP(B3,LookupTable,35,TRUE), this seems to work for some reason. Well, it should work, but FALSE should work as well, and it does for the first 2 instances.
- The value 0.01 definitely exists in LookupTable.
- The (leftmost) values in LookupTable are in ascending order.
- In this file, I previously had an error. Something along the lines of "/xl/calcChain.xml" was removed or something like that. I thought it was repaired, but later I noticed that if I added or removed a column from the sheet that LookupTable was on, Excel would be stuck forever in a calcuation loop. I fixed this, by copying all formulas on the sheet to another sheet. Maybe the issue is deeper than I suspected?