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.

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.

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

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

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Deden" wrote in message
...
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

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

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.

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.

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.

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.

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

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

Very good!

--
Biff
Microsoft Excel MVP


"Jai" wrote in message
...

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