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?

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(A2)

=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.

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 9I 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

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.

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 vbaexpress.com in an article entitled 'Eliminate excess spaces from selection' Thanks so much for putting me on the right track.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History