Microsoft KB Archive/109978

{|
 * width="100%"|

Incorrect LOOKUP Results with Formula in Lookup_Vector

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.01, 2.1, 2.10c, 2.10d, 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for OS/2, version 2.2, 3.0
 * Microsoft Excel for the Macintosh, versions 2.20, 2.2a, 3.0, 4.0

-

SUMMARY
The Microsoft Excel lookup functions VLOOKUP, HLOOKUP, and LOOKUP may return incorrect results if the lookup vector is the result of a formula.

CAUSE
The incorrect result in cell A18 of the example below is 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. This standard is used by Intel coprocessors and most PC-based programs that implement floating-point math.

This is expected behavior and is not a bug or limitation of Microsoft Excel.

WORKAROUNDS
To work around this problem, do either of the following:

 Select the lookup vector (A1:B16 in the example below), copy it, and choose the Paste Special command from the Edit menu, and then select the Values option.

-or-  Incorporate the ROUND function in your formula. For example, to correct the example in the "Steps to Reproduce Problem" section of this article, enter the following formula in A2:A16: "=ROUND(A1+0.1,2)"

Steps to Reproduce Problem
On a new worksheet, type the following:

  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 in B3; however, VLOOKUP returns the value 2 in B2.

For more information regarding the IEEE 754 floating point standard and rounding errors, query in this knowledge base on the following keywords:

"floating-point and standard and rounding and IEEE 754" Additional query words: 4.00a

Keywords :

Version : MACINTOSH:2.20,2.2a,3.0,4.0; WINDOWS:2.01,2.1,2.10c,2.10d,3.0,4.0,4.0a,5.0

Platform : MACINTOSH WINDOWS

Issue type :

Technology :