nested if statement for text search

[mecerrato]

I have this if statement that calculates a due date for me, it works fine but I now have a new situation that created the need to modify to exclude the calculation if column E contains the work "Broker".
=IF(K21<>"","N/A",IFERROR(WORKDAY.INTL(N21,-3,11,Holidays!$D$5:$D$26),""))

Here is what I have tried but it gives me an error, what am I oding wrong?
=IF(K21<>"",if(e21,"<>*brokered*"),"N/A",IFERROR(WORKDAY.INTL(N21,-3,11,Holidays!$D$5:$D$26),""))

 

[Fluff]

Will "Broker" be on it's own in the cell, or part of a larger string?

 

[mecerrato]

Will "Broker" be on it's own in the cell, or part of a larger string?

part of a larger string

 

[Fluff]

Ok, how about

=IF(AND(K21<>"",ISERROR(SEARCH("brokered",E21))),"N/A",IFERROR(WORKDAY.INTL(N21,-3,11,Holidays!$D$5:$D$26),""))

 

[mecerrato]

Ok, how about

=IF(AND(K21<>"",ISERROR(SEARCH("brokered",E21))),"N/A",IFERROR(WORKDAY.INTL(N21,-3,11,Holidays!$D$5:$D$26),""))

Thank you Fluff but that did not work, also I think I should be using OR instead of AND because I want the "N/A" to show if either of those criteria exist. The formula you provided did not result in an N/A as it should based on the work BROKERED existing in E21.

 

[Fluff]

Should you have NA if E21 contains brokered, or like your formula if it does not contain brokered?

 

[mecerrato]

Should you have NA if E21 contains brokered, or like your formula if it does not contain brokered?

if column E21 contains the string BROKERED OR if K21 is NOT blank then the result should be N/A, else the date calculation should occur.

 

[Joe4]

Thank you Fluff but that did not work, also I think I should be using OR instead of AND because I want the "N/A" to show if either of those criteria exist. The formula you provided did not result in an N/A as it should based on the work BROKERED existing in E21.

Did you try changing the "AND" to an "OR" and see if it does what you want?

 

[Fluff]

In that case try

=IF(OR(K21<>"",ISNUMBER(SEARCH("brokered",E21))),"N/A",IFERROR(WORKDAY.INTL(N21,-3,11,Holidays!$D$5:$D$26),""))

 

[mecerrato]

In that case try

=IF(OR(K21<>"",ISNUMBER(SEARCH("brokered",E21))),"N/A",IFERROR(WORKDAY.INTL(N21,-3,11,Holidays!$D$5:$D$26),""))

Thank you Fluff, I would have never been able to get that formula right, I appreciate your time in resolving.