How to match clinical trials to subjects based on eligibility criteria columns?

S_Berkley

Hello,

I have 2 sheets and I want to pull information from sheet to another sheet if certain criteria are met. The first sheet, "Slot List", is a list of clinical trials and the columns contain the study name and certain eligibility criteria patients need to meet to join the study. On the second sheet, "Trial Match", is a list of subjects and the columns contain the eligibility criteria they have. If all of the eligibility criteria in the subject's row (Trial Match sheet) is listed as the eligibility criteria in the study rows (Slot List sheet), I want to pull all potential trials names the subject could be eligible for to the "Eligible Trials" column in the "Trial Match" sheet.

I think I need to use JOIN(COLLECT, but my formulas are not working. I tried simplifying the formula to just the "Cancer Type" criteria using the formula below, but the result is blank.

=JOIN(COLLECT({Study Name}, {Cancer Type}, CONTAINS([Cancer Type]2, {Cancer Type})), ", ")

Sheet 1: "Slot List"

Sheet 2: "Trial Match"

Genevieve P.

Hey @S_Berkley

You're very close! Instead of using CONTAINS, since you're looking in a Multi-Select cell I would suggest using HAS instead.

In either CONTAINS or HAS, we'll want to use @cell instead of {the range again}. This is because we're looking into each individual cell of the previously stated range.

Try this:

=JOIN(COLLECT({Study Name}, {Cancer Type}, HAS(@cell, [Cancer Type]@row)), ", ")

Let me know if that worked for you.

Cheers,

Genevieve