|Your question is just a bit confusing. |
First, your Subject Line says "find and replace" but I don't see where anything is being "replaced". It seems that you are simply trying to "find" a matching value and return it.
Second, you said "return with the text after comma from column B" yet your example seems to indicate that your want the entire contents of the cell returned, not just the text after the comma: so in col C I need "Smith, Bob Adam"
So, assuming that you want to return the entire cell contents, I would try the following procedure. The Columns you use won't matter, as long as you follow the rules for VLOOKUP.
In Column C, enter this formula and drag it down:
=LEFT(B1,FIND(" ",B1,FIND(" ",B1)+1)-1)
This will strip off the e.g. " Adam" (space + Adam) from the values in Column B leaving e.g. Smith, Bob
In column D, enter this formula and drag it down:
=MID(B1,FIND(" ",B1,FIND(" ",B1)+1),LEN(B1))
This will place e.g. " Adam" (space + Adam) in Column D
In Column E, enter this formula:
=VLOOKUP(A1,$C$1:$D$6,1,0) & VLOOKUP(A1,$C$1:$D$6,2,0)
This will lookup the value in A1 in Column C and in Column D and concatenate them back together, returning Cx & Dx, e.g. Smith, Bob Adam
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.