If formula help - assigning names
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
-
Hi @amy_ilearning,
There are a few things that need fixing:
- 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.
- The criteria need putting inside " " if text (you can leave them out if numerical values).
- 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! 🙂
-
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!
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!