VLOOKUP not able to lookup values

Hello experts,

In the attached file, I wanna fill code and no values in (Stationery) sheet with their corresponding from (Full) sheet.

I tried using VLOOKUP function, but as in the attached file, it doesn't seem to be working properly.

Any help?
full.xlsx
MuhajreenAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
The numbers on the Full sheet contains blanks before and after the numbers. Do a search/replace to get rid of the spaces. Some of the spaces before the number are char(160) so just select the space and copy it and then paste in the search/replace window to get rid of them.
0
 
guswebbCommented:
It is working, it just can't find lots of those values from Column A. See attached.
Copy-of-full.xlsx
0
 
MuhajreenAuthor Commented:
Did you try searching for any of the missing values?

The value at row 3 in Stationery sheet (2013198210081) is present at row 41 in full sheet.

In fact, almost all values are present, but VLOOKUP is unable to locate them.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Saqib Husain, SyedEngineerCommented:
Here is a corrected file.
Copy-of-full.xlsx
0
 
MuhajreenAuthor Commented:
Thank you.

I have cleaned values before, thinking that CLEAN function was sufficient to trim spaces.

Now the problem is, when a value is really not available, then excel duplicates the above one. Please have a look on rows 78-144 in the attached file.
0
 
MuhajreenAuthor Commented:
Here is the file
Full.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
Change the last 1 to 0
0
 
MuhajreenAuthor Commented:
Ok great!

Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.