PROBLEM WITH EXCEL 2002 VLOOKUP

[alcole]

I have a problem that is preventing me from executing lookups in excel 2002. I had a major PC problem and lost all my excel work books and have had to reengineer them. I am XP.
In question, I have two spreadsheets in the same work book. The first is a partial list of TSX stock ticker symbols (text) in sorted column (A). The second is a complete and sorted list of TSX stock ticker symbols AND their associated long names (again, text) in two columns (A & B).
In the first spreadsheet and in an adjacent column, I want to display the long names associated with each ticker symbol.
In excel 2000, I used the formula [=VLOOKUP(A2,'TSX Listing'!$A$2:$D$1603,2,FALSE)] where TSX Listing is the complete source. However, in excel 2002, this formula displays the long name for the very last cell (A1598) in each and every cell in the first spreadsheet's 100 row column!
I've confirmed the cells are all in number format and when I use FALSE, I get a #N/A error. Is it me or has excel 2002 been modified?

 

[Aladin Akyurek]

have a problem that is preventing me from executing lookups in excel 2002. I had a major PC problem and lost all my excel work books and have had to reengineer them. Iam XP.
In question, I have two spreadsheets in the same work book. The first is a partial list of TSX stock ticker symbols (text) in sorted column (A). The second is a complete and sorted list of TSX stock ticker symbols AND their associated long names (again, text) in two columns (A & B).
In the first spreadsheet and in an adjacent column, I want to display the long names associated with each ticker symbol.
In excel 2000, I used the formula [=VLOOKUP(A2,'TSX Listing'!$A$2:$D$1603,2,FALSE)] where TSX Listing is the complete source. However, in excel 2002, this formula displays the long name for the very last cell (A1598) in each and every cell in the first spreadsheet's 100 row column!
I've confirmed the cells are all in number format and when I use FALSE, I get a #N/A error. Is it me or has excel 2002 been modified?

Try to clean up the data in case there are excessive spaces or unvisible chars around them by using ASAP Utilities (asap-utilities.com).

BTW, since your lookup table is sorted in ascending order on column A, you can invoke a faster lookup formula:

=IF(LOOKUP(A2,'TSX Listing'!$A$2:$A$1603)=A2,LOOKUP(A2,'TSX Listing'!$A$2:$B$1603),"")

 

[Pekkavee]

I think your A2 cell is in wrong format . Text or number.

Test it by copying something from TSX Listing'!$A$2:$A$1603
into A2.

Regards
Pekka