VLOOKUP not able to lookup values

Posted on 2013-11-29
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?
Question by:Muhajreen
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

Expert Comment

ID: 39685842
It is working, it just can't find lots of those values from Column A. See attached.

Author Comment

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.
LVL 43

Accepted Solution

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.
Technology Partners: 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.

Author Closing Comment

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.

Author Comment

ID: 39685927
Here is the file
LVL 43

Expert Comment

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

Author Comment

ID: 39685957
Ok great!

Thank you

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

717 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