Article ID: 315961 - View products that this article applies to.
This article was previously published under Q315961
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.
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.
To work around this issue, use one of the following methods.
Method 1Use 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:
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 2Turn on the Precision as displayed option in Microsoft Office Excel 2003 and in earlier versions of Microsoft Excel. To do this, follow these steps:
Method 3Type 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.
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:
(https://support.microsoft.com/kb/78113/EN-US/ )XL: Floating-Point Arithmetic May Give Inaccurate Results
(https://support.microsoft.com/kb/214118/EN-US/ )XL: How to Correct Rounding Errors in Floating-Point Arithmetic
Article ID: 315961 - Last Review: January 31, 2007 - Revision: 4.1