Can't get all IF AND parts of formula to work

amy_ilearning

Hi, I am creating a multi-part 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 re-ordering 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")))

Nick Korna

Hi @amy_ilearning

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.