One vlookup returns correct value, next does not

Hi All,

Looking for some help to figure out what is causing our problem. My friend has created the attached excel sheet in Excel 2003. The issue is that the a vlookup does not seem to be finding the correct line for the value from the lookup table when it is returned twice.

Let me explain - The details are contained in the attached workbook. There are three sheets in the workbook - NH3Curve, Samples and Qvalue table.

On the Samples sheet, in cell C12 and C13 you can enter varying values. Go across to F12 and F13 and you will see that they both have the value 22.1. This is where the issue occurs - in cells G12 and G13 the lookup value should be the same - but it is not. G12 is actually returning the value for 22.0 not 22.1. (lines 173 and 174 on the Qvalue sheet). We cannot ferret out why this is happening.

Any help would be greatly appreciated. Sometimes when you look at something you can't see the obvious, so I hope it is that simple.

I have not cross-posted this topic anywhere else.

Thanks for your time.

Hi,

go Tools - Options - Calculation and tick "Precision as displayed". This will now show a warning box, but from then on the sheet will only calculate visible values, i.e. if you have formatted a number to show 2 decimals, it will do all follow on calculations with only the two decimals, not accounting for the hidden decimals.

I still don't understand why this solves the problem, because the calculations leading to your value of 22.1 are straightforward and do not contain any decimals that might upset the picture. As far as I see it, Excel is indeed showing the wrong value, but I'll keep looking as to why.

stumped ....

Hi,
this is really weird.
If you change the lookup formula's last value to FALSE, Excel will return only an exact match. And it seems that the 22.1 that results from

=ABS(C12-D12)

which is 89-66.9 is not the same as the 22.1 that is in your lookup table. It seems to be a teeeny tiiiiiny bit smaller than 22.1, but no matter how many decimals I display, I don't see where. Other than setting precision to "as displayed", as I said above, you could round the value as in

=ROUND(ABS(C12-D12),1)

This will effectively cut off any further decimals and the Lookup will work as expected.

I've started a brand new Excel sheet and done the same kind of calculations and lookups. If I use 89 minus 66.9 it will not find it, if I use 23.1 minus 1 it will find it without a hitch.

Still stumped.

When I run the evaluator on the formula:

=INT(F13)+MOD(F13,1)

...and then run it on:

INT(F12)+MOD(F12,1)

...the MOD section for the decimals clearly shows the variance. One is .10000000009 and the other is .09999999993

I guess that's not a match.

Worse, when I run it on the Qvalue page

=INT(A174)+MOD(A174,1)

.....it returns a decimal value of 1.00000000001

That's three different versions of the same number.

Teylyn is on the mark with this.

McGimpsey & Associates has an article describing this topic here: Penny Off

.

JB, good spotting. Can you please try to explain to me why

89-66.9

returns a different flavour of 22.1

than, say

23.1-1

I attach a spreadsheet with a demonstration how some values of 22.1 are found by the Vlookup, others are not. Apparently 86-63.9 returns a different kind of 22.1 (found by Vlookup) than 87-64.9 (not found by Vlookup).

I think this is clearly a problem with Excel. I look at the spreadsheet and think "Wow. I wonder how many other similar things are happening in Excel, that I have just not spotted yet!?"

ConnexionLost, thanks for that article. Funny thing is that there are no decimals that are formatted away. Take a look at the spreasheet in my previous post and see what I mean.

You should mark the thread as solved if you're happy with the solution. You need to edit the thread and look near the title where only you can see a drop down box to select "Solved".

You can also add to someone's reputation if you liked an answer specifically. On the post that you found helpful, click the scale symbol and follow your gut ...

Please mark your thread solved by doing the following:

- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix. - Change to Solve
- Click Save

Floating points in Excel. Did some more research and some experiements.

MOD(1.9,1) returns 0.9. Fine. Looks good. Same for increments of 1.0 from
MOD(2.9,1) through to MOD(15.9,1). But then it's
MOD(16.9,1) which returns 0.8999999999999990000000
MOD(17.8,1) incrementing by 1 through to MOD(63.9,1) the same. Then the next break at
MOD(64.9,1) which returns 0.9000000000000060000000
The next break is at
MOD(256.9,1) which returns 0.8999999999999770000000
See the pattern?

The funny thing is that if you compare the 22.1 values from the OP's example

=F12=F13

Excel returns TRUE. As does the comparison

=F12='Qvalue table'!A174
and
=F13='Qvalue table'!A174

All these return TRUE. But VLOOKUP apparently sees this differently.