If then statement for a list of individuals


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
    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. 


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!