VLookup OR If Function?

[Juan]

Hi,
hope I can explain what I'm trying to do. Let say, in one sheet column a I
have
Part 10-154
Then in another sheet column A I have part
10-154PBF.
I know when I do a Vlookup it doesn't find it because they don't match.
So is there a way to bring it basically if part is almost same
the difference is the last 3 digits.

So what I'm thinking is it would look like
Column A Column B
10-154 10-154PBF
Or maybe there is a if function i can use?
Would appreciate any help
Thanks,
JH

 

[John Bundy]

If it is always 3 digits and you can use a helper cell, just get the left
digits in another column and do the lookup that way.
For example if 10-154-BPF is in A1 then place this in B2
=LEFT(A1,LEN(A1)-3)
then do the lookup off of that. There are other ways if this is not possible

 

[Billy Liddel]

Hi,
hope I can explain what I'm trying to do. Let say, in one sheet column a I
have
Part 10-154
Then in another sheet column A I have part
10-154PBF.
I know when I do a Vlookup it doesn't find it because they don't match.
So is there a way to bring it basically if part is almost same
the difference is the last 3 digits.

Juan

If all your parts are similar, i.e. you want the first 6 characters to be
the reference try something like this

=VLOOKUP((A13),LEFT(B1315,6),2) and enter it as an array formula
(ctl + shift + Enter)

regards
Peter

 

[L. Howard Kittle]

Hi Juan,

Maybe this little macro will do what you want. Select the cell with 10-154
in it and run the macro (from a button) and it will find 10-154XXX.
However, it will also find AA10-154XXX, which you may not want.

Sub PartNo()
Cells.Find(What:=ActiveCell.Value, _
After:=ActiveCell, _
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
SearchFormat:=False).ActivateEnd Sub
End Sub

HTH
Regards,
Howard