searching for a word(s) within a list
 

I have a list of entries and some have US city names and/or states. I want to
be able to identify those cells that have a match for a US city or state in
the data. For example see the data below:

Jackie Lexington AL
James Jones Australia
Floyd NE
Jamie Salter

If I use the list above and match that against a us city/st list I want only
the 1st and 3rd entries returned as these are the only 3 that match the
criteria and woud find a match in the US city State list. doe this make
sense? Is this clear?

Let me know.

searching for a word(s) within a list
 

This may not be 100% successful....

Assume you have a list of states/cities in the range F2:F7.

The strings to check are in the range A2:An.

Enter this formula in B2 and copy down as needed:

(all on one line)

=IF(COUNT(LOOKUP(2,1/SEARCH
(" "&F$2:F$7&" "," "&A2&" "))),"Y","")

--
Biff
Microsoft Excel MVP


"Philippa" wrote in message
...
I have a list of entries and some have US city names and/or states. I want
to
be able to identify those cells that have a match for a US city or state
in
the data. For example see the data below:

Jackie Lexington AL
James Jones Australia
Floyd NE
Jamie Salter

If I use the list above and match that against a us city/st list I want
only
the 1st and 3rd entries returned as these are the only 3 that match the
criteria and woud find a match in the US city State list. doe this make
sense? Is this clear?

Let me know.

searching for a word(s) within a list
 

What happens if the data is George Washington. Is Washington the state or
the last name?

"T. Valko" wrote:

This may not be 100% successful....

Assume you have a list of states/cities in the range F2:F7.

The strings to check are in the range A2:An.

Enter this formula in B2 and copy down as needed:

(all on one line)

=IF(COUNT(LOOKUP(2,1/SEARCH
(" "&F$2:F$7&" "," "&A2&" "))),"Y","")

--
Biff
Microsoft Excel MVP


"Philippa" wrote in message
...
I have a list of entries and some have US city names and/or states. I want
to
be able to identify those cells that have a match for a US city or state
in
the data. For example see the data below:

Jackie Lexington AL
James Jones Australia
Floyd NE
Jamie Salter

If I use the list above and match that against a us city/st list I want
only
the 1st and 3rd entries returned as these are the only 3 that match the
criteria and woud find a match in the US city State list. doe this make
sense? Is this clear?

Let me know.

searching for a word(s) within a list
 

Hi Philippa,

As your sample shows, you are interested in locating all entries whose last
three characters are a space followed by a 2 letter state abbreviation. If
you data is entered in that format then suppose the data is in column A and
you list all of the state abbrreviations in the range F1:F50, then in a blank
column enter the following array formula:

=IF(OR(RIGHT(A1,3)=" "&$F$1:$F$50),"Yes","No")

And copy it down as far as necessary. To enter it as an array press Shift
Ctrl Enter instead of Enter.

--
Cheers,
Shane Devenshire
Join http://setiathome.berkeley.edu/ and download a free screensaver to help
search for extra terrestrial life.

"Philippa" wrote:

I have a list of entries and some have US city names and/or states. I want to
be able to identify those cells that have a match for a US city or state in
the data. For example see the data below:

Jackie Lexington AL
James Jones Australia
Floyd NE
Jamie Salter

If I use the list above and match that against a us city/st list I want only
the 1st and 3rd entries returned as these are the only 3 that match the
criteria and woud find a match in the US city State list. doe this make
sense? Is this clear?

Let me know.

searching for a word(s) within a list
 

That's one of the reasons I said:

This may not be 100% successful....


--
Biff
Microsoft Excel MVP


"Joel" wrote in message
...
What happens if the data is George Washington. Is Washington the state or
the last name?

"T. Valko" wrote:

This may not be 100% successful....

Assume you have a list of states/cities in the range F2:F7.

The strings to check are in the range A2:An.

Enter this formula in B2 and copy down as needed:

(all on one line)

=IF(COUNT(LOOKUP(2,1/SEARCH
(" "&F$2:F$7&" "," "&A2&" "))),"Y","")

--
Biff
Microsoft Excel MVP


"Philippa" wrote in message
...
I have a list of entries and some have US city names and/or states. I
want
to
be able to identify those cells that have a match for a US city or
state
in
the data. For example see the data below:

Jackie Lexington AL
James Jones Australia
Floyd NE
Jamie Salter

If I use the list above and match that against a us city/st list I want
only
the 1st and 3rd entries returned as these are the only 3 that match the
criteria and woud find a match in the US city State list. doe this make
sense? Is this clear?

Let me know.

Pulling State data from another sheet in workbook
 

This is an excellent formula you provided below, in which I've been using.
What I can't figure out is how to write the formula when pulling data from
another sheet? Example, I'm currently collecting contact info from States
which consist of primary and alternate POC's. One State may have 7 POC's and
another 10. I received them as: Column A, Column B, Column C
AL Primary Jonn Doe
PA Primary Jonn Doe
PA Alternate Jonn Doe

I then consolidate all data and paste the info into sheet 1. For sheet 2, I
simply have it setup in column B all 54 States and Territories abbr and in
column C is where I would like to create the formula. On sheet 2, if column
B1 is AL, then the formula in C1 will look up applicable info in sheet 1 and
indicate the word "received", if info is in range. I'm not concern with the
number of times AL is listed on sheet 1, just want the info to be recognized
in sheet 2 as received. Please provide a formula for me if there is one.
Thanks.


"T. Valko" wrote:

This may not be 100% successful....

Assume you have a list of states/cities in the range F2:F7.

The strings to check are in the range A2:An.

Enter this formula in B2 and copy down as needed:

(all on one line)

=IF(COUNT(LOOKUP(2,1/SEARCH
(" "&F$2:F$7&" "," "&A2&" "))),"Y","")

--
Biff
Microsoft Excel MVP


"Philippa" wrote in message
...
I have a list of entries and some have US city names and/or states. I want
to
be able to identify those cells that have a match for a US city or state
in
the data. For example see the data below:

Jackie Lexington AL
James Jones Australia
Floyd NE
Jamie Salter

If I use the list above and match that against a us city/st list I want
only
the 1st and 3rd entries returned as these are the only 3 that match the
criteria and woud find a match in the US city State list. doe this make
sense? Is this clear?

Let me know.