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. 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.
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.