Bug in VLookup?

  • Thread starter Thread starter Sangita
  • Start date Start date
S

Sangita

0 4.5
1 72
1.1 73
1.2 74
1.3 75
1.4 76
1.5 77
1.6 78
1.7 79
1.8 80
1.9 81
2 82
Above is the table tab1

I want the value from 2nd column against lookup_value using
VLOOKUP(1.4+.4,tab1,2,TRUE)


I get 79 if my lookup value is (1.4+.4) which is 1.8,
hence should return 80. For other combination of 1.8
(1.6+.2, 1.3+.5, 1.1+.7, 1.8), it returns right value,i.e.
80!


Any suggestion? I'm using Office 2000 professional.
 
this is a function of XL's (like every other spreadsheet) using IEEE
floating point math. Just as 1/3 cannot be exactly represented in a
finite number of digits in decimal (i.e., 0.3333333...), most numbers
cannot be exactly represented in binary, which requires rounding. Any
time you're using non-integer math that requires exact comparisons, you
should either (a) compare the absolute difference to a small value,
e.g., instead of

=IF(A1=B1,"Yes","No")

use

=IF(ABS(A1-B1)<1E-10,"Yes","No"

or (b) round the values to an appropriate value, as in this case

=VLOOKUP(ROUND(1.4+0.4,1),tab1,2,TRUE)


For more on XL's rounding see

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