How do I use VLOOKUP to find part of string?

[niatpac]

I am trying to find for example "(3)" in a column of 50 rows where each row
has a name in it. The name for example may be "Jones" in row 1 and "Smith
(3)" in row 8. The "(3)" means something to me in the database. I need to
seperate each name that is marked by the "(3)" and utilize the information in
the next column that corresponds with the "(3)". How do I use VLOOKUP to
find part of this string?

 

[Peo Sjoblom]

=VLOOKUP("*"&"(3)"&"*",A2:B50,2,0)

but that will only find one value, better would be to use autofilter and
custom then from dropdown select contains and put (3) in the criteria, then
select the filtered table and copy somewhere else

 

[niatpac]

I do not think it is possible to use the autofilter in this particular
application because the information is in a form format. Information needs
to be placed in the first form in order for it to be utilized in the final
form. I need every occurrance of the "(3)" in the first form to be
transferred to the second form in a particular column. Thanks for your input
what you gave me so far is helpful.

 

[Peo Sjoblom]

Try this, it assumes that your data that you want to check starts in A2 and
ends in A50 and you want to return all values in B2:B50 where A2:A50
contains (3)

=IF(ROWS(B$2:B2)<=COUNTIF($A$2:$A$50,"*(3)*"),INDEX($B$2:$B$50,SMALL(IF(ISNUMBER(FIND("(3)",$A$2:$A$50)),ROW($A$2:$A$50)-ROW($A$2)+1),ROWS(B$2:B2))),"")


entered with ctrl + shift & enter, then copied down will give all values
that match, adapt to fit any other cell references