Searching for an additional item via an IF statement

[Sarah (OGI)]

I've got the following formula in column O which is designed to find any
variance of the specified company names in column C and enters "Y", otherwise
enters "N".

Another company has joined since writing this, so I'd like to include them
in the list. I'm aware that you can only have up to 7 IF statements,
therefore how can I accommodate Company G when my formula is already maxed
out? - I thought that maybe I could include an OR statement, i.e
IF(ISNUMBER(SEARCH(OR("*Company A*","Company B*"... but this doesn't seem to
work for me?

IF(ISNUMBER(SEARCH("*Company A*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company
B*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company
C*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company
D*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company
E*",C4)),"Y",IF(ISNUMBER(SEARCH("*Company F*",C4)),"Y","N"))))))

Any ideas?

 

[Bob Phillips]

=IF(SUMPRODUCT(COUNTIF(C4,{"*Company A*","*Company B*","*Company
C*","*Company D*","*Company E*","*Company F*","*Company G*"}))>0,"Y","N")

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Teethless mama]

=IF(SUM(COUNTIF(C4,"*Company
"&{"A*","B*","C*","D*","E*","F*","G*"}))>0,"Y","N")

 

[Sarah (OGI)]

Thank you both so much!! - that works great!

=IF(SUM(COUNTIF(C4,"*Company
"&{"A*","B*","C*","D*","E*","F*","G*"}))>0,"Y","N")

 

[Teethless mama]

You're Welcome!

Thank you both so much!! - that works great!

 

[T. Valko]

List the company names in a range of cells.

F2:F7 = company names

=IF(COUNT(LOOKUP(2,1/SEARCH(F2:F7,C4))),"Y","N")