Excel bug?

[AndreasN]

Excel 2000 SP3 fully updated.

I'm going to get out of my mind. I would appreciate any concern to an issue
I am facing with lookup functions:
My table array is $A$2:$B$3 with

A2 = 0,00 B2 = 0,00%
A3 = 2.000,01 B3 = 0,30%.

Now, in another cell, say A6, I type 2.500,10 and in B6 500,09.

In C6 =A6-B6 ( the difference is 2.000,01).

In cell C7 =VLOOKUP(C6;$A$2:$B$3;2;TRUE). The result is 0,30%, which is
correct.

Another scenario now:
A6=2.849,10 and B6=849,09.

VLOOKUP returns 0,00%, which is wrong.

Working around to this issue I found out that it comes up only when the
numbers in A6 and B6 are greater thun
2.511,10 êáé 511,09 respectively, the difference is 2.000,01 (i.e. equal to
A3) and the decimals 01, 10 and 09. No other decimals cause this problem.
Also, the thousnads digit in numbers A3 and A6 must be 1, 2 or 3.
From 4 and above (say 4.000,01 and 4.849,10) everything is OK.
Similar problem appears if I use =INDEX($A$2:$B$3;MATCH(C6;$A$2:$A$3;0);2)
instead of VLOOKUP, regardless of decimals in numbers in A6 and B6 in this
case.
Is it a bug or am I doing something wrong?

 

[JE McGimpsey]

it's probably not a bug, per se, but rather because of small internal
rounding errors (see

http://cpearson.com/excel/rounding.htm

for a detailed explanation). I can replicate your results in XL04. This
formula can be used as a workaround:

=VLOOKUP(ROUND(C6;2);$A$2:$B$3;2;TRUE)

 

[AndreasN]

Thank you very much for your explanation. Please let me investigate this
matter a little further. I made the following test:
I pasted A6:C7 two lines below, in A9:C10. Next I created two different
instances as follows:

A6 = 2.500,10 B6 = 500,09 C6 = 2.000,01
C7 = 0,30%

A9 = 2,849,10 B9 = 849,09 C9 = 2.000,01
C10 = 0,00%

According to C. Pearson's explanations the values in C6 and C9 appear to be
same, but actually they are not, due to background small rounding errors. My
question now is why do I get TRUE if I compare these values in another cell
(i.e. =C6=C9) instead of FALSE, as one would normally expected?
Thank you again.