Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VLOOKUP not able to lookup values

Posted on 2013-11-29
8
Medium Priority
?
317 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
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.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

564 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