VLOOKUP Error when lookup value references a cell that is a sum.

[Joeg101]

Hi,

I am having an issue with Vlookup and would really appreciate some help.

When using the Vlookup function I keep getting random #N/A errors. The lookup value is referring to a cell that is a "sum" of several cells. It works sometimes but other times it throws a #N/A error. When I replace the value with the same exact number it works.

For example:
-A1 is the lookup value (7.20)
-A1 references F14
-F14 =SUM(F1:F13); which is 7.20
-B2 is the Vlookup function and the lookup value =A1 but is throwing the #N/A error.
-When I replace A1 with the actual number 7.20 it works just fine.

Please see attached and thank you for your time!

[FDibbins]

hmm weird, but this fixed it...

A1 =ROUND(F14,2)

[humdingaling]

Fdibbins already provided to workaround

but those that want to know why
Floating point bug
http://support.microsoft.com/kb/315961

[dredwolf]

I think FDibbins got it...you seem to be experiencing 'Round-Off' errors...as decimal numbers do not easily convert to Binary numbers, there is always a 'Round Off at the extreme lowere end of the number, so, even though the numbers may look the same, some where around the 14-15 digit of significance, the rounding is different between the 2 numbers...1 might be "7.2" and the other may be "7.2000000000001" or "7.199999999999999",; when displayed, they both may show as "7.2", but they are not actually "7.2", so they do not equal the condition...

Hope this helps

Edit-
An easy way to check this is change the formatting of A1 or F14 to number--> 15 decimal places, expand the column to display 18 digits, then you can see if the value is actually = to the test number

[FDibbins]

Thanks for the back-up guys

[Joeg101]

I love this forum!

I can't express how much I appreciate your prompt and effective responses.
@FDibbins, your suggestion worked like charm...but you already knew that Thank You!

And a Big thanks to Dredwolf and Humdingaling for the added support. The explanation of why it occurred was very helpful and as result, I am sure it will help me in other function related errors.


Thank You,

-Joe

[FDibbins]

Happy to help and its great to have you here