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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,546
Messages
5,838,034
Members
430,527
Latest member
MyFace2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top