VLOOKUP not working

levcovitz: When I look at your attachment (now), the formula in G5 is =VLOOKUP(G4,C4:D14,2,FALSE), which returns #N/A when G4 is 1.  That requires an exact match with the first parameter (G4).

There is nothing wrong with that, if that is indeed what you require.

The root cause of the problem is:  the values in C5:C14 are calculated; for example, =C4+$D$1 in C5.

And as with most binary arithmetic in Excel, the result is not exactly what it appears to be in some cases.

You can see this by entering =ROUND(C4,1)-C4=0 into A4 and copying down the column.  Note that it returns FALSE(!) for 0.3, 0.8, 0.9 and 1.

(Aside.... =ROUND(C4,1)=C4 returns TRUE in all cases.  That is a misleading behavior of Excel.  I can explain.  But it is a distraction.  So let's "put a pin in it" for now.)

The best fix is to change the formulas in column C.  For example, the formula in C5 should be =ROUND(C4+$D$1,1).

This is a common problem with 64-bit binary floating-point, which is what Excel (and most applications) use to represent numeric values internally.  Most decimal fractions cannot be represented exactly; and the binary approximation of a specific decimal fraction might vary due to the magnitude of the numeric value.

For example, that is why IF(10.01 - 10 = 0.01, TRUE) returns FALSE(!).  In this case, we see why by entering =10.01 - 10 into a cell and formatting with 16 decimal places.  The result appears to be 0.00999999999999979, not 0.01.

But sometimes, we cannot see the infinitesimal difference because Excel formats only the first 15 significant digits.  That is why 0.3 et al appear to be exact in column C, when in fact they are not.

IMHO, the best work-around is:  whenever we expect a calculation to be accurate to some precision, explicitly round the calculation to that number of decimal places, as I showed above.  (And not to an arbitrary number of decimal places like 10, as some people suggest.)