vlookup problem, possibly due to "noise"

J

JPANDRE

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

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

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

or

b) Is there a problem with "noise" in our spreadsheet. I've heard o
the concept before, but haven't seen it's effect for a long time. I
someone can give me an explanation for this phenomonon I would be ver
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 livi
that we have a scorecard with an "error" in it).

Many Thanks!!
 
V

vezerid

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
 
J

JPANDRE

Kostis,

Thanks for taking the time to reply. Here are our numbers as the
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 i
returns the value of 0.0666133814775094

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

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

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

Best Regards
John Andr
 
Top