VLOOKUP bug? Different answer for values vs calculated table.

[Joerd]

I recently found a strange error using VLOOKUP (actually, MATCH does the same):

In cell A6, type 0.1
In cell A7, type =A6+0.1 and copy down to A25
Copy A6:A25 to B6:B25
In cell C6, type =VLOOKUP(B6,A$6:A$25,1,FALSE) and copy down to C25
All looks fine, but now copy A6:A25, and paste the values onto itself (or
fill A6:A25 using Fill/Series with a step of 0.1).

My results are:
Source data Lookup Result
0.1 0.1 0.1
0.2 0.2 0.2
0.3 0.3 #N/A
0.4 0.4 0.4
0.5 0.5 0.5
0.6 0.6 0.6
0.7 0.7 0.7
0.8 0.8 #N/A
0.9 0.9 #N/A
1 1 #N/A
1.1 1.1 #N/A
1.2 1.2 1.2
1.3 1.3 1.3
1.4 1.4 #N/A
1.5 1.5 #N/A
1.6 1.6 #N/A
1.7 1.7 #N/A
1.8 1.8 #N/A
1.9 1.9 #N/A
2 2 #N/A


Can anyone explain the strange behavior at 0.3, 0.8, etc.? And hopefully
present a solution?
Alternatively, if you omit the FALSE (or use TRUE for range_lookup), you
will see a wrong result at 0.8, 0.9, 1.0 and 1.1
( I am running Excel 2003 (11.8307.8221) SP3)

 

[MikeB]

I recently found a strange error using VLOOKUP (actually, MATCH does the same):

  In cell A6, type 0.1
  In cell A7, type =A6+0.1 and copy down to A25
  Copy A6:A25 to B6:B25
  In cell C6, type =VLOOKUP(B6,A$6:A$25,1,FALSE) and copy down to C25
  All looks fine, but now copy A6:A25, and paste the values onto itself(or
fill A6:A25 using Fill/Series with a step of 0.1).

My results are:
Source data    Lookup    Result
0.1    0.1    0.1
0.2    0.2    0.2
0.3    0.3    #N/A
0.4    0.4    0.4
0.5    0.5    0.5
0.6    0.6    0.6
0.7    0.7    0.7
0.8    0.8    #N/A
0.9    0.9    #N/A
1    1    #N/A
1.1    1.1    #N/A
1.2    1.2    1.2
1.3    1.3    1.3
1.4    1.4    #N/A
1.5    1.5    #N/A
1.6    1.6    #N/A
1.7    1.7    #N/A
1.8    1.8    #N/A
1.9    1.9    #N/A
2    2    #N/A

Can anyone explain the strange behavior at 0.3, 0.8, etc.? And hopefully
present a solution?
Alternatively, if you omit the FALSE (or use TRUE for range_lookup), you
will see a wrong result at 0.8, 0.9, 1.0 and 1.1
( I am running Excel 2003 (11.8307.8221) SP3)

Isn't this a manifestation of the same rounding error discussed a few
posts further down?

 

[Joerd]

Got a hint someplace else and solved it. It is a floating point error;
apparently in Excel-land, =(0.2+0.1-0.3) equals 5.5E-17 and not 0.