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 displayedcheck box, and then click OK.
- Click the Microsoft Office Button, and then click
- Click the Advancedcategory.
- Under When calculating this workbook, click to select the Set precision as displayed check box, and then click OK.