PROBLEM WITH EXCEL 2002 VLOOKUP

alcole

New Member
Joined
Dec 4, 2005
Messages
1
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?
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
alcole said:
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

Well-known Member
Joined
May 25, 2004
Messages
1,218
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
:eek: :eek:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,229
Messages
5,571,019
Members
412,354
Latest member
Stj99
Top