Ifs, ANDs, ORs, with text strings, grrrrrrrrr.

[carabale]

I have this formula, which works fine.

=IF(OR(AA3="",AB3=""),"N",IF(AND(AL3<>"",AL3>TODAY()+365),"N",IF(AND(AA3<>"",AB3<>""),"Y")))

However, I need it now to indicate "N" if the inmate is a Sex or Career Offender; so I tried this but is not working:

=IF(ISERROR(FIND("SEX",P2)),IF(ISERROR(FIND("CAREER",P2)),"N",IF(OR(AA2="",AB2=""),"N",IF(AND(AL2<>"",AL2>TODAY()+365),"N",IF(AND(AA2<>"",AB2<>""),"Y")))))

I get a "FALSE" when it encounter the string.

I put the formula togethe from bits and pieces of information; so I know is incomplete or out of order. If It could be simplified would be even greater.

As usual, thanks for your help in advance.

 

[Andrew Poulsom]

Is it?

=IF(OR(ISNUMBER(SEARCH("SEX",P3)),ISNUMBER(SEARCH("CAREER",P3)),AA3="",AB3=""),"N",IF(AND(AL3<>"",AL3>TODAY()+365),"N",IF(AND(AA3<>"",AB3<>""),"Y")))

 

[Scott Huish]

Does this work for you?

=IF(OR(SUM(COUNTIF(P2,{"*CAREER*","*SEX*"})),AA2="",AB2="",AND(AL2>TODAY()+365)),"N","Y")

 

[carabale]

Thank you, thank you, thank you. I was just asked for this report. I thought I had time to figure it out, but I am so glad you all replied so quickly. Saved me from going through 1,300 rows.

I used Mr. Poulsom and it worked marvelous. I have not tried Huish's yet.

But thank you both...