vlookup problem, possibly due to "noise"

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

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 8 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


--
JPANDRE