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?
Comments
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!