vlookup issues

I have a simple vlookup function that looks for a match for the value in A7 of ws1 in the first column of ws2. It is supposed to spit back the corresponding value in the same row of column 2 but it keeps returning the same value. Furthermore the value it does retrieve isn't even the closest match.

Please Login or Register  to view this content.

If I remove the "False" attribute it returns the same value of 39574 and with it I get the error message of Part No. Not Found. I can plainly see the lookup value in the worksheet I'm searching.

I am at a loss to know what can be wrong as this should be a routine and simple operation. Any suggestions would be appreciated. Thanks

There doesn't appear to be any spaces in either, but the lookup value (A7) is populated via concatenation. I wouldn't think that would have any effect but I can think of no other reason why this is occurring. The funny thing is that apparently only one cell in the lookup sheet comes close to matching even though there are far closer candidates.

On evaluating the formula I noticed that there is indeed a space being added at the end of the part number. So the actual search string looks like this...

Please Login or Register  to view this content.

How might I get rid of that?

Thanks for your prompt replies. I tried the Trim and Clean functions to no avail...

Thanks. I've identified the problem and will have to have the guy who wrote the extra space into the formula remove it. Thanks again...