multi level IF formula

Tracey Tume

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

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!

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

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

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.