Issue with V-lookup and possibly pasting data from the web?

Closed
davidplowman Posts 5 Registration date Wednesday April 3, 2013 Status Member Last seen July 8, 2013 - Apr 15, 2013 at 11:54 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 16, 2013 at 10:45 AM
Hi,

I'm hoping someone can help me. Using windows 2010, I'm using data from 500 alphanumeric codes that I copied-and-pasted from the net into a pre-existing Excel Spreadsheet.
Later, since these codes weren't put into the spreadsheet in a "V-look-up friendly" way, I started another sheet, and arranged them so they were all in the first column using the "=" in new sheet and referred to appropriate cell in the other sheet.
Then, with all of my other data lined up, I was able to create a third sheet where I extrapolated all my data and started using lookup.
Except, with about five percent of my data, Vlook-up is falsely returning a "N/A" result. I can go back to my original data, do a "Find" and I can see my data there. In the sheet I created, I can see the data in the sheet I created, but can't locate the data from "find."
What is creating this? I've gone as far as to copy and pasted the data manually into the appropriate cells, and even retying them in, hoping that maybe there was a "hidden character" in what I pasted from the net and that retyping it would somehow eliminate that. I created an "if then statement to see if the two data from my two sheets match each other, and Excel said they were. Still, vlookup doesn't seem to be recognizing the code, even though they are clearly the same.
Any help on this would be appreciated. For the time being I'm just manually ding a find and copying ad passing my data where I get the erroneous N/A's, but it bugs me when things don't work the wa they should, so I'd love a solution!

Thanks,
David


4 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 15, 2013 at 12:12 PM
Hi DavidPlowman,

Consider uploading your file (www.speedyshare.com) so we can take a better look at your specific case.

Did you put your matrix in alphabetical order?

Best regards,
Trowa
0
davidplowman Posts 5 Registration date Wednesday April 3, 2013 Status Member Last seen July 8, 2013
Apr 15, 2013 at 12:26 PM
Trowa:

I'll upload it later this afternoon...

In the meantime, no, my codes are not in any specific order. Would that make a difference? I thought the beauty of Vlookup is so long as the data was in the leftmost column it could be in any order.

Thank you,
David
0
Mehedad Posts 22 Registration date Thursday April 19, 2012 Status Member Last seen April 16, 2013 3
Apr 16, 2013 at 02:01 AM
Sometimes when you copy from the net, there is a trailing <space> behind or before the word(s). You can trim those that you have copied [=TRIM()]. Anyways, its hard to find the actual reason without seeing some sample. Just trying to share my experience here.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 16, 2013 at 10:45 AM
Hi David,

You can do a small test to see alphabetical order is important.

Cell reference: Content
A1: b
A2: a
B1: 2
B2: 1

Formula: =VLOOKUP(A2,A1:B2,2)
Result should be 2 but instead you get #N/B.

Now sort data on the first column of the array and the result will be 2.

So sort your data and see if that solves your query.

Best regards,
Trowa
0