Need a formula to 'find' word in cells of column from a long list

[Deden]

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.

 

[T. Valko]

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)

 

[Deden]

Thanks. this helps. Now I need to find the syntax instructions to refer to
another file and worksheet for the a1:a5 array. I can't find any help in
Excel 2007. I remember the worksheet name must be followed by an ! . I
can't remember the character for the workbook! There's got to be an easy
way to look up these details.
Thanks so much for your prompt answer.
Deden

 

[Ragdyer]

Let XL create the proper path for you!

Enter an equal sign in a cell of the current WB, then navigate to the other
sheet in question, and click in a cell there, and hit <Enter>.

This creates a link to that sheet, from cell to cell, but, more importantly
to you, you can see the exact path in that link formula.

Close the WB, and you'll see the formula expand to show you the *entire*
link.

 

[Deden]

Thanks, this worked - at least got me started. A further refinement would
entail instead of "yes" if the function would put the character string found
in the Z column. Using the example I gave at the outset, if the word 'kat'
was in the array to search, could the function put it in the Z column when it
found hits on 'kathy' and 'kate'? I tried replacing the "yes" in the
function with the array range without success. This may be pushing Excel
capabilities too far. Is there a help webpage I could read to understand
this function better?

I appreciate your advice. Thanks. Deden

 

[T. Valko]

If a cell contains more than 1 match then this formula will return the
*last* match based on the order of the search range.

=LOOKUP(2,1/SEARCH(A$1:A$5,B1),A$1:A$5)

For example:

A1:A5 = names to search for:
sam alice helen jim john

B1 = mike helen anne sam

The formula will return Helen because it is listed *after* Sam in the search
range.

You can use Kat as a search name and it will match Kate or Kathy. However, I
wouldn't get too carried away with this type of "fuzzy matching"!

 

[Jai]

Loving this advice so far. I am using

=LOOKUP(2,1/SEARCH(B1,A$1:A$500),A$1:A$500)

B1 can have upto 5 matches in my data. Is it possible to have 5 columns,
each giving a different result (if multiple results exist)? Thanks for any
help

 

[T. Valko]

What's in B1?

And, show me some examples of what's in A1:A500 that match the search
criteria of B1.

 

[Jai]

B1 is a name or names (e.g. Bill Smith and Richard Jones).

C1:c500 is a list of last names (eg. Smith, Stone, Jones, etc)

So if B1 was Bill Smith and Richard Jones I would like a coulumn giving
Smith and another one givimg Jones.

Trying to avoid macros if possible.

Cheers,
Jai

 

[Jai]

Never mind, figured it out. For those interested

=IFERROR(INDEX($A:$A,SMALL(IF(ISNUMBER(SEARCH(TRIM($B1),$A$1:$A$500)),ROW($A$1:$A$500),""),?)),"")

entred as an array where ? is the number of the result. So the first column
giving the first match would have ?=1, the second column giving the second
match would have ?=2, etc

enjoy

 

[T. Valko]

Very good!