Aug 21 2019 01:58 PM - edited Aug 21 2019 02:29 PM
I am using Excel 2007 and have replicated this error in Excel 2013. On three separate computers in the office, from workbooks made from scratch.
So simplified:
Start with a 1x2 table with value 1.14 and a corresponding value of whatever we want to lookup.
Create a cell as a lookup value that calculates 1.14. e.g. =1+14*.01
Create an index match to lookup this value based on our calculated value.
=Index(1x2Table, Match (1+14*.01, Col1ofTable, 0), 2 )
Returns #NA in both versions of Excel.
I can change the value of 1.14 to 1.13 or 1.15 or whatever and lookup that value based on a calculation and the function returns whatever we want to lookup. Vlookup seems to work fine for all the values as well.
What is going on?
And out of shear curiosity; further tinkering revealed that several multiples of 1.14 and corresponding calculated lookup values also returned the same #NA error. e.g. 2.28
Cells formatted as number with 2 decimals or general, doesn't seem to matter, but copy pasting the value from the calculation does clear the error and return whatever we want to lookup.
Aug 21 2019 02:34 PM
What is going on?
It's just a common floating point error.
Put your calculations inside a round function and it should work.
Explanation (or rant?) by Tom Scott
Aug 21 2019 02:36 PM
Yeah, I should have mentioned that I did that and that fixed it, but I guess my question was more along the lines of why does that seem to happen with 1.14 and not 1.13 or any of my other values.
Aug 21 2019 02:51 PM
Okay, so I watched the linked video, and again the error is along the lines of what I was thinking, but I guess I was under the impression that Excel's Cell Formatting fixed this type of error. Does it not? And why does Vlookup work where Match fails?
Aug 21 2019 03:21 PM
I found that, provided the calculation process is identical, the discretisation error will be the same and the match will work. Thus
=1+14*0.01 finds
=1+14*0.01
but not
1.14
… and conversely.
For any comparison, the numbers should be integers, not floating point.
You might be interested to know that
1+14*0.015625
and
1.21875
work interchangeably because dividing by factors of two does not generate rounding error.
Aug 21 2019 03:26 PM - edited Aug 21 2019 03:28 PM
VLOOKUP works if you skip optional 4th parameter, i.e. use approximate search. With it equal to TRUE it returns an error.
Cell formatting is only formatting, it doesn't affect binary representation of the number .