Formula to search and return cell based on criteria not working

Hi all,

I'm trying to make a formula that says "if cell A contains cell B AND does not contain cells C D or E, then cell A, else 1". My first effort only had one extra criteria:

=IF((ISERROR(SEARCH(F$1,'Com. Development FN'!$M8))=TRUE)+(ISERROR(SEARCH(F$694,'Com. Development FN'!$M8))=FALSE),1,'Com. Development FN'!$M8)

And it works like a charm. When I attempted to expand my criteria however (i.e. "does not contain cells C D or E", as opposed to "does not contain cell C"), all of it sudden it fails to work probably and always returns a value of "1":

=IF((ISERROR(SEARCH(F$1,'Com. Development FN'!$M2))=TRUE)+(ISERROR(SEARCH(F$694,'Com. Development FN'!$M2))=FALSE)+(ISERROR(SEARCH(F$695,'Com. Development FN'!$M2))=FALSE)+(ISERROR(SEARCH(F$696,'Com. Development FN'!$M2))=FALSE),1,'Com. Development FN'!$M2)

Anyone have any ideas what the issue here might be? I'm afraid I can't attach the spreadssheet itself as it contains confidential info, but I could potentially take screenshots of certain bits if that would help. Thanks very much for taking the time read!

Thanks very much for the replies guys; unfortunately NBVC's formula has a similar problem to my original one, and Special-K's has an error in it somewhere (not sure why changing the + to a * would result in an error though - weird).

I'm going to keep trying and if I come with a solution I'll post it here.

NVBC's formula does work, I was just applying it wrong!

Thanks NVBC - saved me a ton of time that would otherwise have been spent manually searching through thousands of rows of data :D

Sorry to drag up an old topic, but can anyone explain what this part of NVBC's formula means:

MATCH("*"&Sheet3!F$694:F$696&"*"

I understand that the ampersands are concatenation orders, but why are they necessary? And why are they concatenating the " "*" "? Why an asterisk? Why in quotation marks?

Also, what exactly does the ":" mean? Is the formula matching that string of cells, or one cell after the other? Is it, in essence, performing multiple matches one after the other?

Thanks!

Please mark this thread as solved.

Awesome thanks! I understand now