Solved Vlookup Issues

August 25, 2011 at 14:12:39
Specs: Windows 7, i5/4 GB
I am currently using Vlookup to pull actual inventory numbers from a worksheet and put them on the inventory numbers I have pulled from our database. The command I am using is =VLOOKUP(A2,'Actual Count'!A:C,3)

The problem is when it pulls the data from the other spreadsheet it appears to only look at the first four number of the item number and finds a best fit match which is wrong. If I add false to the end of the formula it returns and n/a for all the values even though there are matches that I can see with my own eyes.

Is there a way to expand how far the vlookup will look in the part number so I get an accurate execution of the formula?

See More: Vlookup Issues

Report •

August 25, 2011 at 14:59:02
If you are looking for an exact match, you need to use FALSE (or 0) as the range_lookup argument. If it is TRUE (1) or omitted (as it appears to be in what you posted) an approximate match will be returned.

Now, since the range_lookup argument has been omitted, and an approximate match is being returned, it seems to me that what appears to be a match that you can see with your own eyes really isn't.

Here's a test:

Find 2 cells that appear to be a match, e.g. A2 and 'Actual Count'!A5.
Enter this in a cell:

=A2='Actual Count'!A5

If that does not return TRUE, then it's not a match.

If it's not a match try things like:

=LEN('Actual Count'!A5)

Maybe there's an extra space, or a hidden character or something.

Let me know what you find.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 26, 2011 at 05:37:11
OK. I found a sample data set of 3 matches and 1 mismatch. I ran the commands you suggested and when I run the LEN I get way different numbers. So there are hidden characters. We are talking about 7000 rows of data so is there a way to clear hidden characters and just focus on the part numbers?

The data I looked at is as follows

Part Number 1 Part Number 2 LEN(Part Number 1) LEN (Part Number 2)
0101082 0101082 31 7
0113074K 0113074K 31 9

I appreciate the response and you were right. They do appear the same but are in fact different. Hopefully there is a way to remove hidden characters?

Thanks, Zach

Report •

August 26, 2011 at 08:37:33
✔ Best Answer
You can try the TRIM and/or CLEAN functions.

Read the Help files for those 2 functions so that you understand what they do.

You can even nest them: =TRIM(CLEAN(A1)) or =CLEAN(TRIM(A1))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

August 26, 2011 at 10:56:20
Problem solved. With your help on what function to use I was able to decipher that hidden characters were causing the issue. I then found a free macro that trims entire selections of data from in an article entitled 'Eliminate excess spaces from selection'

Thanks so much for putting me on the right track.

Report •

Ask Question