Article ID: 191114
Article Last Modified on 6/27/2007
APPLIES TO
- Microsoft Excel 98 for Macintosh
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
Additional query words: XL98
Keywords: kbprb KB191114
