[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

VLOOKUP not able to lookup values

Posted on 2013-11-29
8
Medium Priority
?
314 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39685874
Here is a corrected file.
Copy-of-full.xlsx
0
 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

650 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