The VLOOKUP and HLOOKUP functions fail to find a number in a list in Excel
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:
- 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.
- 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.
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:
78113 XL: Floating-Point Arithmetic May Give Inaccurate Results
214118 XL: How to Correct Rounding Errors in Floating-Point Arithmetic
Article ID: 315961 - Last Review: 01/31/2007 19:56:01 - Revision: 4.1
Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition, Microsoft Excel 97 Standard Edition
- kbnofix kbprb KB315961