Solved

VLOOKUP not able to lookup values

Posted on 2013-11-29
8
272 Views
Last Modified: 2013-11-29
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
0
Comment
Question by:Muhajreen
  • 4
  • 3
8 Comments
 
LVL 9

Expert Comment

by:guswebb
Comment Utility
It is working, it just can't find lots of those values from Column A. See attached.
Copy-of-full.xlsx
0
 

Author Comment

by:Muhajreen
Comment Utility
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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
Comment Utility
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Here is a corrected file.
Copy-of-full.xlsx
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Closing Comment

by:Muhajreen
Comment Utility
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
 

Author Comment

by:Muhajreen
Comment Utility
Here is the file
Full.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Change the last 1 to 0
0
 

Author Comment

by:Muhajreen
Comment Utility
Ok great!

Thank you
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now