Log In
Register
Home
New Member
September 9, 2023
Offline
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.
July 16, 2010
Offline
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
New Member
September 9, 2023
Offline
Hoping it attaches this time. Thank you so much!
VIP
Trusted Members
December 7, 2016
Offline
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
July 16, 2010
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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
