Excel Nested If, Or, Then function

tkeiffer

New Member
Joined
Aug 5, 2005
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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"
 
Upvote 0
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")
 
Last edited:
Upvote 0
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"
 
Upvote 0
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"
 
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,157
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top