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.