sub string search returning multiple values

Hi All,

First time on the board, glad I found this site!

I'm trying to build a formula that searches for multiple sub-strings and returns specific values based on what it finds.

I started off by first writing; =IF(OR(SEARCH("Prep", H3,1),SEARCH("Tail",H3,1),SEARCH("Custom",H3,1),),"Flagged"," ") I thought this would at least return the word "Flagged", but its not working.

I then tried; =IF(OR(SEARCH("*"&Prep&"*", H2,1),SEARCH("*"&Tail&"*",H2,1),SEARCH("*"&Custom&"*",H2,1),),"Flagged"," "). Returned #Name?

then; =IF(OR(H2="*"&Prep&"*",H2="*"&Tail&"*",H2="*"&Custom&"*"),"Custom"," ") Returned #Name?

then; =IF(H2="*"&Prep&"*","Prep Session",IF(H2="*"&Tail&"*","Tailored",IF(H2="*"&Custom&"*","Custom"))) returned #Name?

I'm stumped ...

I want to accomplish the last if statement logic by returning a value for each sub-string parameter found. Any help is appreciated.

Thanks,
ABL

Try it this way instead:

=IF(COUNTIF(H2,"*prep*")+COUNTIF(H2,"*tail*")+COUNTIF(H2,"*custom*")>0,"Flagged","")

Hope this helps.

Pete