If formula help - assigning names

Options

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?

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    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
    amy_ilearning ✭✭
    edited 11/09/23
    Options

    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
    Options

    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
    Nick Korna ✭✭✭✭✭✭
    Options

    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")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!