vlookup in Excel 2002

[Roger]

I am getting in consistent results using vlookup in Excel
2002. This problem did not occur in previous releases of
the program.

Example using the same vlookup formula: =+VLOOKUP
(D512,'7.15'!$B$2:N577,4). In approximately 1/3 of the
cells the answer is incorrect. Data is sorted in
ascending order. To make matters worse the formula is
returning a values that are no where in the lookup table.

 

[Jerry W. Lewis]

You need to provide more information. What is in D512 in an example
when it works? What is in D512 in an example when it fails? (also
describe the contents of the target cell in '7.15'!$B)

My guess is that when it fails, D512 contains a decimal fraction that
cannot be exactly represented in binary. I am also skeptical that the
return value is nowhere in the lookup table. It would probably be from
the line above or below the line you expected it to use.

If I am correct about this being an issue about floating point
representations, then you can probably solve the problem by rounding the
values in '7.15'!$B$2:B577 and changing your formula to
=VLOOKUP(ROUND(D512,d),'7.15'!$B$2:N577,4)
where d is the same number of decimal places you rounded to in
'7.15'!$B$2:B577

Two additonal comments:
- the plus sign immediately following the equals sign is redundant
(that syntax is an unnecessary holdover from Lotus)
- since there are no dollar signs on the trailing cell in
'7.15'!$B$2:N577 the lookup table will change if you copy the formula to
another cell.

Jerry