Forum Discussion
VLOOKUP not working
Hello! I am having a problem with the VLOOKUP formula. I get an error even when the value exists in the interval. In the attached spreadsheat, the error happens when looking for 0.3, 0.8, 0.9 and 1, and I don't know why.
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.)
6 Replies
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.)
JoeUser2004 wow, thank you very much for this explanation. I realized that the problem had to do with the values coming from formulas, but I would never be able to fix it myself.
MaryBwrote: ``VLOOKUP defaults to exact match``.
No, it does not. From the VLOOKUP support page: ``range_lookup (optional) [....] Approximate match - 1/TRUE [....] This is the default method``.
No matter. When I look at the Excel file attachment to levcovitz's posting, the formula in G5 that returns #N/A is =VLOOKUP(G4,C4:D14,2,FALSE). That does indeed specify an exact match. But it is not the "default" per se.
you typed a formula correct but in end you have to use true function because of approximation match in number in decimal
if number is in whole we can use exact match (false).
i have attached a file check it
Khizar_Hayatwrote: ``you have to use true function [which specifies an] approximation match [....] if number is in whole we can use exact match (false)``.
When I look at the Excel file attachment to levcovitz's posting, the formula in G5, which returns #N/A when G4 is 1, =VLOOKUP(G4,C4:D14,2,FALSE). In contrast, the formula in your Excel file attachment is =VLOOKUP(G4,C4:D14,2,TRUE). Just the opposite of what you say.
Using TRUE (approximate match) works for you only by coincidence. And by coincidence, it works in all cases except =VLOOKUP(0.3,$C$4:$D$14,2,TRUE). That returns 2.1 instead of 1.8, as we should expect. See my response to levcovitz for an explanation.
