View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
T. Valko T. Valko is offline
external usenet poster
 
Posts: 15,768
Default 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.