Need a formula to 'find' word in cells of column from a long list
One way...
Assume the list of names to search for is in the range A1:A5 - sam alice
helen jim john
Enter this array formula** in Z1 and copy down as needed:
=IF(COUNT(SEARCH(A$1:A$5,B1)),"Yes","")
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
--
Biff
Microsoft Excel MVP
"Deden" wrote in message
...
In a spreadsheet of mostly textual information, one column consists of
multiple names. I need to identify matches against a list of about 300
names.
A less complex example:
The list of names to look for: sam alice helen jim john
Column cells
B1 - kate mark julie thelma
B2 - mike helen anne anson - 1 hit on 'helen'
B3 - jerry kathy sally sam - 1 hit on 'sam'
I need a mechanism to look for names from the list and identify matches by
adding a flag, 'yes', in another column, Z
Match or Find or Search, seem to only identify a single specified string.
A
workaround would be to construct the formula and run it individually for
each
name. Is that my only option? Which function would be best?
Thanks.
|