Article ID: 315961
Article Last Modified on 1/31/2007
APPLIES TO
- Microsoft Office Excel 2007
- Microsoft Office Excel 2003
- Microsoft Excel 2002 Standard Edition
- Microsoft Excel 2000 Standard Edition
- Microsoft Excel 97 Standard Edition
This article was previously published under Q315961
SYMPTOMS
When you use a VLOOKUP formula or HLOOKUP formula to locate a calculated LOOKUP value, the formula returns #N/A. This occurs even when a comparison of the LOOKUP value and the value in the lookup table returns TRUE.
CAUSE
This problem occurs because of the precision of your computer, which must represent and manipulate numbers in binary. Microsoft Excel compares the exact binary representation of the numbers, rather than their decimal equivalents, which are displayed on the screen. Therefore, rounding errors can occur in the binary representation of the numbers that are not evident when you compare the decimal values visually.
WORKAROUND
To work around this issue, use one of the following methods.
Method 1
Use the TRUNC function in your VLOOKUP or HLOOKUP function to force the LOOKUP value to be evaluated to a specific number of decimal places. For example, type the VLOOKUP function as follows:
=VLOOKUP(TRUNC(A1,2),C1:D5,2,FALSE)
This formula rounds the LOOKUP value in cell A1 to two decimal places and then looks for it in the lookup table (C1:D5). The formula returns the data from the second column of the lookup table.
Method 2
Turn on the Precision as displayed option in Microsoft Office Excel 2003 and in earlier versions of Microsoft Excel. To do this, follow these steps:
- On the Tools menu, click Options, and then click the Calculation tab.
- Click to select the Precision as displayed check box, and then click OK.
Turn on the Set precision as displayed option in Microsoft Office Excel 2007. To do this, follow these steps:
- Click the Microsoft Office Button, and then click Excel Options.
- Click the Advanced category.
- Under When calculating this workbook, click to select the Set precision as displayed check box, and then click OK.
Turning on this option forces Excel to permanently change all stored values in a workbook from full precision (15 digits) to the precision displayed by the format that is applied to the cell. For example, if the number 1.12345 is typed in a cell and then formatted to display only 1.12, the decimal places not displayed (.00345) are lost.
Method 3
Type the LOOKUP value, instead of referring to a cell that has been calculated. You can type the LOOKUP value into a cell that is referenced by the formula, or you can type the LOOKUP value directly into the formula.
MORE INFORMATION
For additional information about floating-point errors and how to correct them, click the article numbers below to view the articles in the Microsoft Knowledge Base:
78113 XL: Floating-Point Arithmetic May Give Inaccurate Results
214118 XL: How to Correct Rounding Errors in Floating-Point Arithmetic
Additional query words: XL2007
Keywords: kbnofix kbprb KB315961
