An IF statements that operates if a cell CONTAINS any one of a LIST of words...

[DrPhilBurns]

[FONT=&quot]I know how to write an IF statement that looks at a string of text, and will perform an action if the string CONTAINS a certain "word" using
[/FONT][FONT=&quot]=IF(ISNUMBER(SEARCH("*word*",A1)),"word","")
[/FONT][FONT=&quot]
[/FONT][FONT=&quot]But
Is it possible to write an IF statement that looks at a string of text, and will perform an action if the string CONTAINS any one of a list of certain words?
e.g.
[/FONT][FONT=&quot]I have a list of addressess which include the country. I want to do something based on whether the address is in Europe
using similar notation as above, I need something like:
[/FONT][FONT=&quot] =IF(ISNUMBER(SEARCH("*

• *",A1)),"europe","")
  where LIST is a range of cells containing all the European countries "France, Germany, Denmark, ...."[/FONT]

 

[Aladin Akyurek]

Try to describe a problem in words instead of a possible, non-working solution.

Guessing at your problem, try:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&EuropeList&" "," "&A1&" "),EuropeList)

 

[DrPhilBurns]

I have figured out the first part of my problem using the formula (which works):
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Europe,J8))),"Europe","NOT")
where "Europe" is a named list including all the European countries - this returns "Europe" if the address contains a country in the list and "Not" if not.


Next question is:
Is it possible to take the above solution one step further and return the actual matched country from the address?
e.g. if the address contains "France", then the formula returns "France" and so on for each country in the named list Europe?

 

[Aladin Akyurek]

I have figured out the first part of my problem using the formula (which works):
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Europe,J8))),"Europe","NOT")
where "Europe" is a named list including all the European countries - this returns "Europe" if the address contains a country in the list and "Not" if not.


Next question is:
Is it possible to take the above solution one step further and return the actual matched country from the address?
e.g. if the address contains "France", then the formula returns "France" and so on for each country in the named list Europe?

See post #2 ...