Index, Match, Contains with a Keyword


I am trying to search a master employee spreadsheet and return the email of the director of the employee. Some directors have other words in their title such as Director of XX Department. The problem is the employee spreadsheet doesn't have a column for the employees' director, so I need to search the positions of the entire sheet and if the position contains the word "Director" and it matches the same Department as the Employee, then return the Email that matches the name of the Director.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!