Search code examples
google-sheetsformulas

Multiple Nested IF(AND(OR) Statements

I'd like to write a formula for multiple IF conditions that will show in my cell S23.

  • IF: H23= "X" or "removed"
  • THEN: S23= "X"

.

  • IF: I23= "EWS" or "removed"
  • THEN: S23= "EWS"

.

  • IF: H23= "X" or "removed" AND I23="EWS" or "removed"
  • THEN: S23= "X + EWS"

.

My formula:

=if(H23="chex", "chex", IF(I23="EWS", "EWS", IF(H23="removed", "chex", (IF(I23="removed", "EWS", ""))))))))

It works fine, but doesn't capture the last AND condition. I'm not using =IF(OR(H23, ">0" etc since this cell can also contain "NA". I only want it to capture the "X" or "removed" conditions.

Any idea how to add the last condition: IF both H23 and I23 contain "removed" or "X" / "EWS", respectively, then the result is "X+EWS"?

Solution

  • This worked: =IF(AND(OR(H23="x",H23="removed"),OR(I23="EWS",I23="removed")),"x+EWS",IF(OR(H23="x",H23="removed"),"x",IF(OR(I23="EWS",I23="removed"),"EWS","")))

    I modified @pnuts original formula: =IF(AND(OR(H23="X",H23="removed"),OR(I23="EWS",I23="removed")),"X+EWS",IF(OR(H23="X",AND(H23="removed",I23<>"removed")),"X",IF(OR(I23="EWS",I23="removed"),"EWS","TBD")))

    Removed parts in bold: AND( H23="removed" ,I23<>"removed")

    Specifically, this argument:

    from IF(OR(H23="X",AND(H23="removed",I23<>"removed")),"X",

    to IF(OR(H23="X",H23="removed"),"X",

    Was a team effort. Thank you @pnuts and @user0 for your help! :)