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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

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

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ 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"

  • Tracey Tume
    Tracey Tume ✭✭✭✭✭

    hi @Bassam.M Khalil

    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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    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.


    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!