multi level IF formula

I am trying to formulate a cell to look at who is the Assembler and then return a corresponding Factory Team Name based on a group of names - auto populating the Team name. Something like:
IF Assembler@row = Ayesha, Connor, Al, Loretta, John and Martin then cell Factory Team Name@row = Factory Team 1, IF Assembler@row = Kelly, Matt, Guy, Peter, Phil and Hayden then cell Factory Team Name@row = Factory Team 2, IF Assembler@row = Craig, Jack, Jason, Rob, Grant Jnr and Warren then cell Factory Team Name@row = Factory Team 3.
This was a guess but failed
=IF(OR(Assembler@row = "Grant Jnr", "Eru", “Connor", “Martin", "Ayesha", "Kelly", "John", "Loretta"), "Factory Team 1"; IF(OR(Assembler@row = "Guy", "George", “Al", "Jack", "Craig", "Warren", "Mark", "Matty V", "Peter"), "Factory Team 2"; IF(OR(Assembler@row = "Graeme", "Tim", "Hayden", "Phil", "Jaxon", "Jack J", "Todd", "Justin", "Prab", "Raul", "Dante", "Matt"), "Factory Team 3")
Any help will be greatly received.
Tracey
Answers
-
Hi @Tracey Tume
I hope you're well and safe!
I'd recommend creating a lookup table instead in the same or another sheet.
Would that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi @Tracey Tume
Hope you are fine, to add to the excellent advice of Andrée Starå i created the following formula as per your request, you can add all the names.
=IF(OR(Assembler@row = "Grant Jnr", Assembler@row = "Eru"), "Factory Team 1", IF(OR(Assembler@row = "Guy", Assembler@row = "George"), "Factory Team 2", "Factory Team 3"))
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Firstly, thank you for taking the time to respond to my request for help!
Based on your advice, I have tried the formula below which still comes up #UNPARSEABLE
Would you have any other ideas where I am going wrong, please?
=IF(OR(Assembler@row = "Grant Jnr", Assembler@row = "Eru", Assembler@row = “Connor", Assembler@row = “Martin", Assembler@row = "Ayesha", Assembler@row = "Kelly", Assembler@row = "John", Assembler@row = "Loretta"), "Factory Team 1", IF(OR(Assembler@row = "Guy", Assembler@row = "George", Assembler@row = “Al", Assembler@row = "Jack", Assembler@row = "Craig", Assembler@row = "Warren", Assembler@row = "Mark", Assembler@row = "Matty V", Assembler@row = "Peter"), "Factory Team 2", IF(OR(Assembler@row = "Graeme", Assembler@row = "Tim", Assembler@row = "Hayden", Assembler@row = "Phil", Assembler@row = "Jaxon", Assembler@row = "Jack J", Assembler@row = "Todd", Assembler@row = "Justin", Assembler@row = "Prab", Assembler@row = "Raul", Assembler@row = "Dante", Assembler@row = "Matt"), "Factory Team 3"))
Thank you Tracey
-
Hi @Tracey Tume
Hope you are fine, could you export your sheet to excel and send it to my Email (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question. because i copied your formula to my sheet and it's didn't work, i think maybe you have a problem with the column name, so when you send the excel to me i will import it and check the problem and give you the exact formula.
My Email : [email protected]
the following is a screenshot from my sheet show you how the formula is working.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
Check out the Formula Handbook template!