We have a vlookup formula which does not seem to recognise that a number in our table array is the same as the lookup value (by sight to about 20 decimal places).

If we link to another spreadsheet and in that spreadsheet subtract the value in the table from the lookup value, then mulitply the result by 10^14, it returns a value of 0.07 and we think this is why the vlookup isn't returning the correct value.

I guess the question is:
a) Is there a known problem with the vlookup function when numbers appear to be the same

or

b) Is there a problem with "noise" in our spreadsheet. I've heard of the concept before, but haven't seen it's effect for a long time. If someone can give me an explanation for this phenomonon I would be very grateful. I have a dim recollection of this being a problem with excel, but I'm having a job explaning it to my Director (who is obviously livid that we have a scorecard with an "error" in it).

Many Thanks!!!

JPANDRE,
Excel has a precision limit of 15 digits. I wonder what numbers you
have in your lookup table that, after displaying 20+ decimal digits,
you still do not see any difference. Are they all from 0 to 1? Then
this would explain what you say. I have seen "noise" be produced by
rounding functions: you thought you had 2.01 and in reality it was
2.010000000000046.

Are the values in your table array expected to be in the key column of
the lookup table? Are they derived through a rounding function? Are you
using the "exact" version of vlookup? (i.e. VLOOKUP( , , , FALSE). If
not are the numbers in the key column of the lookup table ordered
ascending?

HTH
Kostis Vezerides

Kostis,

Thanks for taking the time to reply. Here are our numbers as they appear on the screen, expanded out:

From the table array:
-1.09000000000000000000000000000000

The Lookup Value
-1.09000000000000000000000000000000

If I take one from the other, and multiply the result by 10^14 it returns the value of 0.0666133814775094

Our Vlookup formula is as follows, as the table is in ascending order (-10 is at the top, and -1.09 is at the bottom, with about 12 points in between).

=+VLOOKUP(I28,'Tables'!$D$376:$F$388,3,TRUE)

We have fixed the scorecard now by placing a "round" function in the table array. Interestingly, to prove a point to my director who was sceptical about it being due to "noise", the number of decimal places I put to round to was 120, and the formula now returns the "correct" result for our scorecard.

Best Regards
John Andre