If formula help - assigning names

amy_ilearning

Hi! I'm trying to create an IF formula where if certain values appear in the Product or State1 columns, it lists the same of an employee for assignment.

Above are the columns I'm working with. The formula will be in the Employee column. Basically, if Product = Vermont Red, I want to assign Slim Julian. If Product = Carolina Gold AND State1 = NC, I want to assign Paula Smith.

Here's what I've (unsuccessfully) come up with:

=IF([Product@row] = Vermont Red, "Slim Julian", IF(AND([Product@row] = Carolina Gold, [State1@row] = NC, "Paula Smith"))

There will be instances where there is no one assigned.

I keep getting an Unparseable error. What am I doing wrong?

Nick Korna

Hi @amy_ilearning,

There are a few things that need fixing:

1. The [ ] brackets go round the column name only, the @row stays outside them and it isn't required for column names where there is no space (or seemingly a number at the end). Hence you'll only need it around State1, but not Product here.
2. The criteria need putting inside " " if text (you can leave them out if numerical values).
3. You need to close off your AND statement before the "If True" portion.

So your formula should be:

=IF(Product@row = "Vermont Red", "Slim Julian", IF(AND(Product@row = "Carolina Gold", [State1]@row = "NC"), "Paula Smith"))

Of course, you can keep adding on additional IF & IF(AND) statements for additional names as required.

Hope this helps, but if you've any problems/questions then just post! 🙂

amy_ilearning

Ah, perfect! All the examples I could find had IF values that were numbers, so I didn't know about the " ".

What if I wanted to include an IF AND formula for if a State1 was NOT NC? Would that be:

=IF(Product@row = "Vermont Red", "Slim Julian", IF(AND(Product@row = "Carolina Gold", [State1]@row = "NC"), "Paula Smith"), IF(AND(Product@row = "Carolina Gold", [State1]@row <> "NC"), "John Davis"))

Thank you @Nick Korna!

amy_ilearning

Another question: I've added another IF AND but for a not equals to. For some reason, it's only recognizing the first two IF statements, not the last one. Here's what I have:

=IF(Product@row = "Vermont Red", "Slim Julian", IF(AND(Product@row = "Carolina Gold", [State1]@row = "NC"), "Paula Smith", IF(AND(Product@row = "Carolina Gold", [State1]@row <> "NC"), "John Davis")))

What am I doing wrong?

Nick Korna

Your formula has worked fine for me on copy/pasting it in, so I'm not 100% sure what the problem is. If you isolate it:

=IF(AND(Product@row = "Carolina Gold", [State1]@row <> "NC"), "John Davis")

What result do you end up with (in a row that should have John Davis appear)?

This should give the same result as your formula:

=IF(Product@row = "Vermont Red", "Slim Julian", IF(AND(Product@row = "Carolina Gold", [State1]@row = "NC"), "Paula Smith", IF(AND(Product@row = "Carolina Gold", NOT([State1]@row = "NC")), "John Davis")))