If then statement for a list of individuals

Cyndie LeClair

Good day!

I am trying to reference one cell in my spreadsheet that has a drop down list of 30+ people in the list.  I want to create another cell using the if then formula to say if person a, person, c, and person h are in the first cell, provide this name for their manager.  I have 4 managers that I need to divide this group into.  What is the best way to write this?

Mike Wilday

Assuming the employee is in row23,

=IF(OR([dropdown column name]23= "Person a", [dropdown column name]23= "Person c", [dropdown column name]23= "Person h"), "Manager 1", IF(OR([dropdown column name]23= "Person b", [dropdown column name]23= "Person d"), "Manager 2", IF(OR([dropdown column name]23= "Person e", [dropdown column name]23= "Person f", [dropdown column name]23= "Person g"),"Manager 3", IF(OR([dropdown column name]23= "Person h", [dropdown column name]23= "Person i"), "Manager 4"))))

Would do the trick... but you might also want to explore using Vlookup and keep a list of users and their managers on another sheet.

=Vlookup([employee dropdown column]23 {Referenced Table List from other sheet}, 2) 

You have to click the refrence another sheet link in the dropdown as you enter =vlookup - see screenshot. 

It makes for a much shorter formula and its more manageable because you can keep a solid list of employees and their managers on a separate sheet. And if the manager changes, or you add an additional one, you won't have to update your long formula... you just make the changes to that one sheet.