What's in B1?

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

--
Biff
Microsoft Excel MVP


"Jai" wrote in message
...
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" wrote:

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"!

--
Biff
Microsoft Excel MVP


"Deden" wrote in message
...
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" wrote:

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.