Assuming you want to get all names that contain a word starting with
the letters in the input cell, as your example implied ...
Assume list of names in A2:A90, input cell is C2
In D2 enter =SMALL(IF(ISERROR(SEARCH(" "&$C$2,"
"&A2:A90)),"",ROW(A2:A90)),ROW(1:1)) as a array formula (enter with
CTL-SHIFT-ENTER) and drag down as many rows as you think you will have
entries in your resultant list. this will give you row numbers of
names containing words that start with the string entered in C2, and
#NUM errors following the end of the list.
In E2 enter =IF(ISNUMBER(D2),INDEX(A:A,D2),""), and drag down as far as
the formulas go in column D. This will give you all the names that
meet your criterion.
If you want to get all the names that simply contain the string entered
in the input cell, eliminate the two instances of " "& from the first
formula so that it reads
=SMALL(IF(ISERROR(SEARCH($C$2,A2:A90)),"",ROW(A2:A90)),ROW(1:1))
You can hide column D if you don't want the #NUMs to be visible, or you
can combine the two formulas into one if you don't want to use the
helper column (D).
Another approach is to use a macro and the custom option of autofilter
to simply filter the litst to show only those names that match the
input string, but that does not generate a separate list as you seemed
to want.
HTH
DOR
