Index, Match, Contains with a Keyword

Christina Riot

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.

BullandKhmer

Maybe use index collect with contains

=IFERROR(INDEX(COLLECT({email}, {name}, CONTAINS([ref col]@row, @cell)), 1),"")

Christina Riot

Thank you so much but that didn't seem to do the trick. I'm attaching a snip from the spreadsheet. So i want to collect the Director's email where the Department matches.

Jeff Reisman

@Christina Riot Try something like this:

=INDEX(COLLECT(Email:Email, Department:Department, Department@row, Position:Position, CONTAINS("Director", @cell)), 1)

English: Collect the Email address for the row that has the same department as this row, and where the Position column contains "Director".

Christina Riot

Hi Jeff! thank you so much but that didn't seem to work. :(

Jeff Reisman

https://community.smartsheet.com/discussion/comment/371836#Comment_371836

Was there a particular error message you got?