Index Collect + If Contains Multiple Partial Terms in Column
I am trying to write a formula to pull the employee name (Full Name) from my source sheet into my target sheet if these criteria are met:
· Employment Status field in source sheet lists Active or Pending for the employee
· Company field in source sheet lists Company X or is blank for the employee
· Job Profile field in source sheet lists “Clerk” or “Admin” as part of the Job Profile term for the employee
Source sheet setup:
Full Name Job Profile Employment Status Company
Anderson, Jane Clerk III Active Company X
Bell, Mike Clerk II Active Company X
Smith, Joe Clerk I Pending Group Y
Jones, Sara Manager Active Company X
Drake, Henry Admin I Termination – Completed Group Y
Lomax, Kay Clerk I – Bilingual Active Company X
Franklin, Steve Admin II Pending
Target sheet setup:
Full Name Job Profile Employment Status Company Type Value Date
INDEX/COLLECT is the formula type I'm familiar with to pull the name, but I cannot find an example of adding an IF/CONTAINS with it. Am I even on the right track?
I appreciate any help provided.
Help Article Resources
Check out the Formula Handbook template!