Excel Nested If, Or, Then function

[tkeiffer]

Hello,
This is kind of a nested mess that I can't figure out. I'm looking for help because I don't have the skills to engineer this function.
In cell Z2, (If cell X2 equals the value in W2, AND any of the cells P2/Q2/R2 have the value "Red" or the value "Orange", then put "Critical Attention" in the cell in cell Z2) OR (If cell X2 equals the value in U2, and any of the cells P2/Q2/R2 have the value "Red" or the value "Orange", then put "Immediate Attention" in the cell Z2) else "Lower Risk"

Could someone possibly help me out with this super complicated nested If function?

 

[tkeiffer]

Actually, I need to add one more to the nest. I'm sorry
In cell Z2, (If cell X2 equals the value in W2, AND any of the cells P2/Q2/R2 have the value "Red" or the value "Orange", then put "Critical Attention" in the cell in cell Z2) OR (If cell X2 equals the value in U2, and any of the cells P2/Q2/R2 have the value "Red" or the value "Orange", then put "Immediate Attention" in the cell Z2) OR (If cell X2 equals the value in T2, and any of the cells P2/Q2/R2 have the value "Red" or the value "Orange", then put "Lower Risk" in the cell Z2", ELSE "No Risk"

 

[Eric W]

Try:

=IFERROR(IF(SUM(COUNTIF(P2:R2,{"Red","Orange"})),INDEX({"Lower Risk","Immediate Attention","No Risk","Critical Attention"},MATCH(X2,T2:W2,0)),"No Risk"),"No Risk")

or

=IFERROR(IF(SUM(COUNTIF(P2:R2,{"Red","Orange"})),CHOOSE(MATCH(X2,T2:W2,0),"Lower Risk","Immediate Attention","No Risk","Critical Attention"),"No Risk"),"No Risk")

 

[tkeiffer]

Thank you. I'm sorry. I was oversimplifying the request and as a result I see the formula is use a range for W2, U2, T2 are actually in values in cells on a separate tab called "Categories". The actual need is this:

In cell Z2, (If cell X2 equals the value in Tab=Categories-A4, AND any of the cells P2/Q2/R2 have the value "Red" or the value "Orange", then put "Critical Attention" in the cell in cell Z2) OR (If cell X2 equals the value in Tab=Categories-A3, and any of the cells P2/Q2/R2 have the value "Red" or the value "Orange", then put "Immediate Attention" in the cell Z2) OR (If cell X2 equals the value in Tab=Categories-A3, and any of the cells P2/Q2/R2 have the value "Red" or the value "Orange", then put "Lower Risk" in the cell Z2", ELSE "No Risk"

 

[tkeiffer]

What is wrong with me, I need to change the last part to Categories-A2

In cell Z2, (If cell X2 equals the value in Tab=Categories-A4, AND any of the cells P2/Q2/R2 have the value "Red" or the value "Orange", then put "Critical Attention" in the cell in cell Z2) OR (If cell X2 equals the value in Tab=Categories-A3, and any of the cells P2/Q2/R2 have the value "Red" or the value "Orange", then put "Immediate Attention" in the cell Z2) OR (If cell X2 equals the value in Tab=Categories-A2, and any of the cells P2/Q2/R2 have the value "Red" or the value "Orange", then put "Lower Risk" in the cell Z2", ELSE "No Risk"

 

[Eric W]

Try this:

=IFERROR(IF(SUM(COUNTIF(P2:R2,{"Red","Orange"})),CHOOSE(MATCH(X2,Categories!$A$2:$A$4,0),"Lower Risk","Immediate Attention","Critical Attention"),NA()),"No Risk")

 

[tkeiffer]

Eric, you are an Excel God thank you

 

[Eric W]

You're welcome!