XL98: Incorrect LOOKUP() Results with Formula in Lookup_Vector (191114)


The information in this article applies to:

  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q191114

SYMPTOMS

The Microsoft Excel 98 Macintosh Edition lookup functions VLOOKUP(), HLOOKUP(), and LOOKUP() may return incorrect results if the lookup vector is the result of a formula.

CAUSE

The incorrect result may be caused by a rounding error due to the use of the IEEE 754 floating-point standard. The IEEE 754 standard is a method of storing floating-point numbers in a compact way that is easy to manipulate.

WORKAROUND

To work around this problem, do either of the following:
  • Select the lookup vector, on the Edit menu click Copy. Then, click Paste Special on the Edit menu, select the Values option in the Paste Special dialog box, and click OK.

    Note, this will remove your formulas and replace them with the values returned by the formulas. -or-

  • Incorporate the ROUND() function in your formula.
Please see the "More Information" section of this article for a demonstration of this issue.

STATUS

This is by design of Microsoft Excel 98 Macintosh Edition.

MORE INFORMATION

To see an example of this issue, do the following.

Enter the following in Sheet1 of a new workbook: 
   A1:  .1          B1:  1
   A2:  =A1+0.1     B2:  2
   A3:  =A2+0.1     B3:  3
   A4:  =A3+0.1     B4:  4
   A5:  =A4+0.1     B5:  5
   A6:  =A5+0.1     B6:  6
   A7:  =A6+0.1     B7:  7
   A8:  =A7+0.1     B8:  8
   A9:  =A8+0.1     B9:  9
   A10: =A9+0.1     B10: 10
   A11: =A10+0.1    B11: 11
   A12: =A11+0.1    B12: 12
   A13: =A12+0.1    B13: 13
   A14: =A13+0.1    B14: 14
   A15: =A14+0.1    B15: 15
   A16: =A15+0.1    B16: 16
   A17:
   A18: =VLOOKUP(0.3,A1:B16,2)
With the formula in A18, you would expect an exact match to be found in A3, which would return the value 3 (from cell B3); however, VLOOKUP() returns the value 2 (from cell B2).

To return the expected value of 3, change your formula in A2 to the following formula:

=ROUND(A1+0.1,2)

Then, fill this formula down to cell A16.

For additional information, please see the following article in the Microsoft Knowledge Base:

78113 XL: Floating-Point Arithmetic May Give Inaccurate Results

Modification Type:MajorLast Reviewed:9/11/2002
Keywords:kbprb KB191114
©2002 Microsoft Corporation. All rights reserved.