# Error using IF/AND/OR Statement with multiple conditions

#### d87

##### New Member
I am trying to write a formula based on the following conditions and it is not working

• IF A1 = "S", and B1 = 254 or 127, Use LIST NOLIST
• IF A1 = "F" or "M", and B1 = 254, and C1 is even, Use LIST EVEN254
• IF A1 = "F" or "M", and B1 = 254, and C1 is odd, use LIST ODD254
• IF A1 = "F" or "M", and B1 = 127, and C1 is even, Use LIST EVEN127
• IF A1 = "F" or "M", and B1 = 254, and C1 is odd, use LIST ODD127
Here's the formula I am currently using. I am receiving an #VALUE error

=IF(OR(AND(B1=127,B1=254),AND(A1="S")),NOLIST,IF(OR(AND(A1="F",A1="M"),AND(B1=127,C1="T")),EVEN127,IF(OR(AND(A1="F",A1="M"),AND(B1=127,C1="F")),ODD127,IF(OR(AND(A1="F",A1="M"),AND(B1=254,C1="T")),EVEN254,IF(OR(AND(A1="F",A1="M"),AND(B1=254,C1="F")),ODD254,"")))))

I also tried this which did not work either

=IF(AND(B1=254,C1="T",OR(A1="F",A1="M")),EVEN254,IF(AND(B1=254,C1="F",OR(A1="F",A1="M")),ODD254,IF(AND(B1=127,C1="T",OR(A1="F",A1="M")),EVEC127,IF(AND(B1=127,C1="F",OR(A1="F",A1="M")),ODD127,IF(AND(B1=254,B1=127),OR(A1="S")),NOLIST,""))))

My initial formula was working, and then I realized I had to add the conditions for the ODD/EVEN numbers so after I added those conditions to the formula, it stopped working. Here's my original formula

=IF(OR(AND(B1=127,B1=254),AND(A1="S")),NOLIST,IF(OR(AND(A1="F",A1="M"),AND(B1=127)),EVEN127,IF(OR(AND(A1="F",A1="M"),AND(B1=254)),EVEN254,"")))

I changed the list names for simplicity, so the original list names did not contain even/odd.

Can anyone help me solve this?

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### mole999

##### Moderator
should EVEN254 bw text, so requires "" to wrap ?

#### Fluff

##### MrExcel MVP, Moderator
If nolist, even254 are named ranges try
=IF(AND(A1="S",OR(B1={254,127})),list,IF(OR(A1={"F","M"}),IF(B1=254,IF(C1="T",even254,ODD254),IF(B1=127,IF(C1="T",even127,ODD127))),""))

#### d87

##### New Member
should EVEN254 bw text, so requires "" to wrap ?
That doesn't seem to make a difference, but thank you for your help!

#### d87

##### New Member
If nolist, even254 are named ranges try
=IF(AND(A1="S",OR(B1={254,127})),list,IF(OR(A1={"F","M"}),IF(B1=254,IF(C1="T",even254,ODD254),IF(B1=127,IF(C1="T",even127,ODD127))),""))

That does seem to work, but the only issue is that I forgot to mention I am putting in a data validation as a list and arrays cannot be used. Thank you for your help!

#### Fluff

##### MrExcel MVP, Moderator
In that case do it like
OR(B1=254,B1=127)

Replies
3
Views
280
Replies
4
Views
62
Replies
3
Views
71
Replies
3
Views
85
Replies
8
Views
117

1,128,140
Messages
5,628,937
Members
416,354
Latest member
JojoMaque

### 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?

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