Searching for an additional item via an IF statement
 

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?

Searching for an additional item via an IF statement
 

=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)



"Sarah (OGI)" wrote in message
...
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?

Searching for an additional item via an IF statement
 

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


"Sarah (OGI)" wrote:

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?

Searching for an additional item via an IF statement
 

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

"Teethless mama" wrote:

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


"Sarah (OGI)" wrote:

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?

Searching for an additional item via an IF statement
 

You're Welcome!

"Sarah (OGI)" wrote:

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

"Teethless mama" wrote:

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


"Sarah (OGI)" wrote:

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?

Searching for an additional item via an IF statement
 

List the company names in a range of cells.

F2:F7 = company names

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

--
Biff
Microsoft Excel MVP


"Sarah (OGI)" wrote in message
...
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?