I have a really odd thing happening with a vlookup, where it is randomly bringing back incorrect values. The only common denominator I can find is it's only happening with whole numbers. But even that is random. Some whole #'s are fine. I've included examples of both in the attached sample file.
Hi Brandie,
Welcome to our forum!
The file didn't get attached. You need to click 'start upload' after selecting your file, then wait for the grey check mark beside the file size before clicking 'submit reply'.
However, from what you described, my guess is that the numbers you're seeing that you then type over and are correctly found, are text, not numbers. When you type over them it fixes the number format to a number.
These numbers may have an apostrophe at the front of them that's only visible from the formula bar, or they might have leading or trailing spaces.
If you're still stuck, come back and attach your file and we'll take a look.
Mynda
Hoping it attaches this time. Thank you so much!
Hello,
Can’t really explain why it behaves like this, but if you round the calculation in column G you get correct result back from your lookup. But I agree, it is strange.
Br,
Anders
It'll be a floating point problem. Although what you see in the cell might be 200.000000' for example, in the actual underlying XML file it's probably 199.9999999
Hence, why rounding fixes the issue.
Mynda
Where did the data in column D come from? That's where the problem actually originates, since as Mynda said, although the values look like whole numbers, internally they are being stored as values like 199.9999999999994 and 200.00000000003
