Using an array as "find_text" in a FIND formula / or kind of a reverse VLOOKUP

[koledgegirl]

This seems theoretically simple but I can't wrap my head around it:

In A1 I have a text string, ie "Hello World!"
I want to find "World" within the text string, as part of an IF(ISERROR...) formula:
= IF(ISERROR(FIND("World", A1),"","Hooray!")

Now, I want to find EITHER "Hello" or "World", the way I would do it is by nesting:
=IF(ISERROR(FIND("Hello",A1)),IF(ISERROR(FIND("World",A1)),"","Hooray!"),"Hooray!")

Now, I have a full database with various phrases in column A and a list of select words such as "Hello", "World", etc = "List". I want to search for ANY ONE OF THE words on the List in each database entry. If my List is 7 words or less, I can continue nesting, but what do I do when it's longer?
It seems that there's some sort of an array usage that needs to happen here, but I am just guessing... and of course I tried but it doesn't work.

Any thoughts???

 

[xld]

Try this array formula

=IF(SUM(IF((ISNUMBER(SEARCH($M$1:$M$5,A1))*($M$1:$M$5<>"")),1))>0,"Hooray","")

After typing the formula, confirm with Ctrl-Shift-Enter, not just Enter.

 

[Domenic]

Try...

=IF(OR(ISNUMBER(FIND({"Hello","World"},A1))),"Hooray","")

or

=IF(OR(ISNUMBER(FIND(List,A1))),"Hooray","")

Note that the second formula needs to be confirmed with CONTROL+SHIFT+ENTER.

 

[koledgegirl]

ahhh YES! Thank you!!!
I love MrExcel forums

also I just found another way, slightly less direct, but the idea is the same:

{=IF(SUM(COUNTIF(A1,List))>0,"Hooray!","")}
Where the list would consist of "*Hello*", "*World*", etc

 

[lpaton]

Is there a way to do this and find out which value in the list matched? Kind of like a FIND or a SEARCH function but using many text strings to search within the single lookup value?

I am trying to lookup the state (list of long state names) from a cell with a bunch of other info, e.g.:

"Northern Arizona $72 s/f", "California Condo", "Bulk Washington District of Columbia"

The text doesn't have a fixed position nor a leading character.

Assistance greatly appreciated! ~W

 

[Domenic]

"Northern Arizona $72 s/f", "California Condo", "Bulk Washington District of Columbia"

Are these three separate strings, in three separate cells? If so, assuming that A2 contains the text string Califronia Condo, let J2:J53 contain the list of state names, then try...

=LOOKUP(9.99999999999999E+307,FIND(" "&$J$2:$J$53&" "," "&A2&" "),$J$2:$J$53)

Note that FIND is case-sensitive. If you don't want the formula to be case-sensitive, replace FIND with SEARCH.

 

[lpaton]

It works brilliantly, thank you so much!! I now have to study the formula to see if I can understand why.

Great assistance, very much appreciated, thanks again!! ~L