Excel 2007 : VLOOKUP formula returning #N/A - even though value is in table

**ali22** · 06-27-2011, 04:02 AM

I have a formula as below:

=VLOOKUP(B25,OverallRatings!A1:B500,2,FALSE)

Where B25 is formatted to be a number restricted to two decimal points (ranging between 0.01 and 5.00), and is a calculation of other cells
=(B15*D2)+(B24*C2)

And the OverallRatings sheet has a full list of the range 0.01 to 5.00 sorted in order.

In several instances the result returned is #N/A, yet not all.

It appears from the Show Calculation Steps box that the issue is related to the formatting of the number. The box appears with the following Evaluation:

VLOOKUP(3.0625,OverallRatings!A1:B500,2,FALSE)

The actual cell is displaying 3.06, not 3.0625.

Any advice on how to resolve this, would be greatly appreciated.

**Roger Govier** · 06-27-2011, 04:07 AM

Hi

Just because the cell is formatted to show 2 decimal places, does not mean that the underlying formula result is 2 places.

Change
=(B15*D2)+(B24*C2)
to
=ROUND(=(B15*D2)+(B24*C2),2)