Vlookup not finding all the matches

**dvelopment** · 11-27-2008, 08:08 AM

I am trying to use a vlookup to compare two sets of stock tickers. The vlookup formula seems to work up until around the 1000th cell, then it seems to miss most of the matches. For example ZEP in column A is in A2013, and in column C its in C5944, but the Vlookup doesn't find it and simply puts it as #N/A. I'm not sure why this is happening. It seems to work for about half of the tickers list I'm looking for; the first 1000 or so. Anyway I'm attaching the the excel file, if anyone has any ideas, that would be great, thanks.

**Shijesh Kumar** · 11-27-2008, 08:22 AM

Select the column from where u r trying to lookup the value.

Click on Data --> Text To column.

Select Fixed width (second option)

Then click on Finish button...

**dvelopment** · 11-27-2008, 08:45 AM

Thanks for the response Shijesh. It seems to work except that the data in the first 1000 cells comes back as #N/A and the second half shows up with the proper matches. Also its not working accross the board in the bottom half also. KNDI which appears in A1024 also appears in C3071 but it still comes up as #N/A. Any more ideas? Thanks

**Shijesh Kumar** · 11-27-2008, 09:57 AM

Originally Posted by dvelopment

Thanks for the response Shijesh. It seems to work except that the data in the first 1000 cells comes back as #N/A and the second half shows up with the proper matches. Also its not working accross the board in the bottom half also. KNDI which appears in A1024 also appears in C3071 but it still comes up as #N/A. Any more ideas? Thanks

Select Column A....
Click on Data --> Text to Column and then select Fixed with and Click Finish

Select Column C..
Click on Data --> Text to Column and then select Fixed with and Click Finish

Its working perfectly fine for me.. in ur sample sheet..

Also I noticed there is extra space in each cell.. So in cells it appear as A but its actually " A"...

**dvelopment** · 11-27-2008, 08:50 AM

This is what appears to be happening. It seems the matches that the vlookup finds before the I selected fixed width disappear afterword and appear as #N/A and the matches that it finds after I select fixed with, disappear afterword. Any ideas on how to fix this.

**Max, Singapore** · 11-27-2008, 09:43 AM

Think you could try doing this on your original data "as-is"

In B2, normal ENTER:
=ISNUMBER(MATCH(TRUE,INDEX(TRIM(A2)=TRIM($C$2:$C$5974),),0))
Copy B2 down to last row of data in col A. Col B will return TRUE for items in col A found in col C, FALSE otherwise. The checking implicitly TRIMs both the lookup values in col A and the reference list in col C, for improved robustness in matching.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
--