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"

S_Berkley

So far, I am able to pull potential clinical trials for the patients if there is only 1 criterion listed in each column per study (formula copied below). I am having trouble changing the formula to allow for multiple criteria to be listed in the columns for the clinical trials. I have been trying out HAS and CONTAINS functions, but so far I am getting #incorrect argument set results.

=JOIN(COLLECT({Study Names}, {Cancer Type}, [Cancer Type]@row, {Molecular}, Molecular@row, {Prior Lines}, [Prior Lines]@row), ", ")

KPH

Hi @S_Berkley

How about

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

The three parts in bold are the changes to make this work with your multiple select dropdowns.

Sheet 1

Sheet 2