Can't get all IF AND parts of formula to work
Hi, I am creating a multipart IF AND formula. Basically, I'm looking at two columns: Product and State
IF Product = Science, assign to Mike Foster
IF Product = Mathematics AND State = certain states, assign to Paula Smith
If Product = Mathematics AND State = other certain states, assign to Bill Peace
I've come up with the formula below; however, it's only recognizing the first and second IF statements, not the third. It's assigning ALL Mathematics Products to Paula Smith, none to Bill Peace regardless of the State. It's almost like it's not accounting for the State. What am I doing wrong? Am I listing the states incorrectly?
Adding: the Product and State columns are Text/Number. Also, I've tried changing AND to OR in both, and I've tried reordering the statements, and it still only recognizes Mike Foster and Paula Smith.
=IF(Product@row = "Science", "Mike Foster", IF(AND(Product@row = "Mathematics", [State]@row = "AK, WA, OR"), "Paula Smith, IF(AND(Product@row = "Mathematics", [State]@row = "PA, CA"), "Bill Peace")))
Answers

You need to add in some OR statements where you have multiple states such as this formula below:
=IF(Product@row = "Science", "Mike Foster", IF(AND(Product@row = "Mathematics", OR([State1]@row = "AK", [State1]@row = "WA", [State1]@row = "OR")), "Paula Smith", IF(AND(Product@row = "Mathematics", OR([State1]@row = "PA", [State1]@row = "CA")), "Bill Peace")))
You can do the OR portion first in the AND bracket if you prefer:
=IF(Product@row = "Science", "Mike Foster", IF(AND(OR([State1]@row = "AK", [State1]@row = "WA", [State1]@row = "OR"), Product@row = "Mathematics"), "Paula Smith", IF(AND(OR([State1]@row = "PA", [State1]@row = "CA"), Product@row = "Mathematics"), "Bill Peace")))
Hopefully this helps, post if you're unsure on anything.
Help Article Resources
Categories
Check out the Formula Handbook template!