Error using IF/AND/OR Statement with multiple conditions

d87

New Member
Joined
Jul 3, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
should EVEN254 bw text, so requires "" to wrap ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 3, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
In that case do it like
OR(B1=254,B1=127)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,931
Messages
5,621,661
Members
415,849
Latest member
PhoenixRising2015

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
Top