Calculation errors in Excel 2000

[01Patrik]

Hi,

I have a user who is experiencing the strangest problem...

She has a database which contains several hundreds of rows of data. Column "L" contains unique numbers.

Sheet 2 (col "a") contains a number cells with the same kind of numbers as previously mentioned in sheet1!L:L.

The main issue is to exclude all rows in "sheet 1" where the data in column L matches a number in sheet2!A:A.

By some reason, the original author of the template chose to do this by using VLOOKUP in another column in sheet 1:

=vlookup(L2,sheet2!A:A,1,FALSE)

(she used to apply on all rows, filter and delete)

This works fine on all computers which I've tried it on - except on hers (Win 2K pro, XL 2000). The formula returns N/A. The problem is NOT due to formatting as text / numbers.

It also worked on her computer when I replaced "sheet2!A:A" with "sheet2!A1:A25", but next week the problem was back and the same solution refused to work...

The issue has been resolved by using countif instead, which also is a better solution, filtering all 1's and deleting the rows.

=countif(sheet2!A:A,L2)



But I can't get the original problem out of my head - does anyone have a clue why the problem appeared?
Please note that I don't need any solution to the original issue, I need an explanation if anyone has one... simply can't accept that Excel behaves unrationally (or seems to, at least), since I've always been able to resolve all previous problems one way or the other.

// Patrik

 

[xlbo]

interesting - the only time vlookup should return N/A is if there is no match. 99.9% of the time, errors here are due to text/numbers or trailing spaces. If you are sure that neither of these is the issue, please feel free to email me the file and I'll have a look
Geoff dot Barraclough at punchpubs dot co dot uk

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SkipVought]

Patrik,

This problem often occurs when you have "numbers" for either colating or matching when there are both numbers and strings of numeric characters.

A sort of numeric characters would result in this, for instance...
[tt]
1
10
100
2
20
200
[/tt]
The binary value of the NUMBER 1 is 1.
The binary value of the CHARACTER 1 is 110001.

So you can see you a sort or lookup could get screwed up.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[01Patrik]

Geoff, I'm waiting for the user to mail me the file... I'll forward it once I get it.

Skip, I see your point, and that might have been the problem if the function would be based on "range_lookup" = true. However, this is not the case, the function's looking for an exact match (FALSE).

// Patrik