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.
Answers
-
Maybe use index collect with contains
=IFERROR(INDEX(COLLECT({email}, {name}, CONTAINS([ref col]@row, @cell)), 1),"")
-
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.
-
@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".
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff! thank you so much but that didn't seem to work. :(
-
Was there a particular error message you got?
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!