How to include two matches with Index/Match

Sam McDonald

I want to be able to return the contract status of all employees assigned to a job.

I have a column called Employee Name which is a multi select drop down where employees are assigned to a job. I then split out those employees on a single service agreement into another column called SSA Employee using:

=IF([Contract Type Helper]@row = "FTE", "Not Applicable", JOIN(COLLECT({Employee Name}, {Contract}, NOT(CONTAINS(@cell, "FTE" )), {Employee Name}, CONTAINS(@cell, [Employee Name]@row)), " ")))

I then have another column which returns the Contract Status

=IF([SSA Employee]@row = "Not Applicable", "Not Applicable", JOIN(COLLECT({Contract Status}, {Employee Name}, CONTAINS(@cell, [SSA Employee]@row)), ", ")))

As people can be employed on an SSA for more than one job, I then need to incorporate this into the above formula so it's only returning the employee contract status when the job # matches. Any ideas?!

Paul Newcome

It sounds like you are going to want an INDEX/COLLECT. Are you able to provide some screenshots for context?

Sam McDonald

Sure Paul, this is one that works fine but only when each employee has one contract.

If the person has two contracts, then both of the statuses come back, whereas I only want the one which matches the Job# which is a column on both sheets.