VLOOKUP fails to find some values

Hi all,

Started using VLOOKUP today and everything was working beautifully until I used it to match one specific number out of a long list of numbers. All data is formatted as General. There are no spaces in the values. I put together (and attached) a test sheet with a sampling of different numbers to maybe see a pattern in why all my values worked except for one.

The sheet works like this:
Initial values are entered in Column A.
Column B is a formula to subtract Column A from 0.945: =0.945-A2
Column C is this formula: =VLOOKUP(B2,$E$2:$F$23,2,FALSE)
Columns E and F are the data. For testing, each value for Column B was added to the data table in each respective row.
And I added a test in Column G to check whether each value in Column B matches the value in the data table (to rule out spaces, formatting issues, etc.)

Out of 22 somewhat random numbers, testing for a range of numbers that don't work, 11 gave me #N/A.

See the attachment, please. Is there something I'm missing? It seems to me that, all else being the same, the specific values shouldn't cause an issue with VLOOKUP.

Thanks, Shawn

I don't claim to fully understand it, but it's related to the Floating Point Precision issue.
https://support.microsoft.com/en-us/...sults-in-excel


Try using ROUND on the formula in B
B2 and filled down
=ROUND(0.945-A2,5)

Change the 5 to whatever level of decimal accuracy you want

You are suffering from floating point errors in the representation of fractions, where something that looks like 0.853, for example, might actually be stored by Excel as 0.852999999999 or 0.853000000000001 which do not give an exact match. If you change the formula in B2 to this:

=ROUND(0.945-A2,5)

and copy this down, then the VLOOKUP formulae all return the expected values.

Hope this helps.

Pete

Awesome, that works!

Thanks to you both!

So, if they are stored in Excel, as in your example, 0.85299999999, why do they match in the test in column G, but fail to match for VLOOKUP?

Like I said I don't 'fully' understand it.

Long Complicated Story, in a short summary.

Many decimal values cannot be 'Precisely' represented by a binary system (your PC is a binary system).
So some rounding happens (this is the part that I can't follow, how/when/why which values get rounded).
Every system has different methods of dealing with this problem, there are even differences within the same program.
Different functions within Excel may handle this problem differently than others. So while one function sees them as equal, it's possible other functions won't.

It is also to do with the different number of bits (binary digits) used in logical comparisons and in arithmetic. If you put this formula in H2:

=B2-E2=0

and copy down, you would expect them all to produce TRUE results, but you will see FALSE values against some of the numbers (i.e. against every value which produces #N/A in your VLOOKUP formula). Arithmetic operations use a greater number of bits to do their calculations compared with logical operations (known as Double Precision, the results of which are converted back to Single Precision), so if the errors are very small then logical operations (like =B2=E2), which use Single Precision may not detect them, but arithmetic operations (and this is what the VLOOKUP function uses) will do. Excel also has its own rules (heuristics) to deal with very small values, especially to determine values that are very close to zero, but they are known only to the Microsoft programmers that wrote them.

You just have to be wary of these errors, and take steps to avoid them when dealing with fractions.

Hope this helps.

Pete.

Ok, very informative, thanks again!

Well, on the bright side, as an engineer, I'm not at all concerned with precision in arithmetic calculations... oh, wait...