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

[Closed]
Report
Posts
5
Registration date
Wednesday April 3, 2013
Status
Member
Last seen
July 8, 2013
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
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 replies

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
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
Posts
5
Registration date
Wednesday April 3, 2013
Status
Member
Last seen
July 8, 2013

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
Posts
22
Registration date
Thursday April 19, 2012
Status
Member
Last seen
April 16, 2013
3
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.
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
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