To work around this issue, use one of the following methods.
Use the TRUNC
function in your VLOOKUP
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.
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 displayedcheck 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
- Click the Advancedcategory.
- 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.
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.