Solved

VLOOKUP not able to lookup values

Posted on 2013-11-29
8
280 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
ID: 39685842
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
ID: 39685862
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
ID: 39685865
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
ID: 39685874
Here is a corrected file.
Copy-of-full.xlsx
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Closing Comment

by:Muhajreen
ID: 39685923
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
ID: 39685927
Here is the file
Full.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39685954
Change the last 1 to 0
0
 

Author Comment

by:Muhajreen
ID: 39685957
Ok great!

Thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

895 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

19 Experts available now in Live!

Get 1:1 Help Now