Check for text in cell from separate look-up list and return text from same row but different column in the look-up list

Question

I am hoping for a final bit of help on by bird research study. I want to search for specific diagnoses (text thread) in a patient's list of diagnoses and then return the body system associated with that diagnosis.

In the example below, Columns A, B, and C are the look-up list. Column D is the patient number. Column E is the search-in list. Columns F and G are the desired output lists.

In this example, the first patient (D2,E2) was diagnosed with FIBROSIS, MYOCARDIAL and SHOCK, PULMONARY. From the All Diagnoses List we can find these two diagnoses and see that FIBROSIS, MYOCARDIAL (A3) affects the cardiovascular system (CARDIO) (B3=Y). Therefore, the desired output is that the cardiovascular system for the first patient is affected, Y (F2=Y). The first patient (D2,E2) also has SHOCK, PULMONARY. SHOCK, PULMONARY (A2) does not affect the CARDIO system (B2=blank) but does affect the respiratory system (RESP) (C2=Y). So the output for the respiratory system for the first patient is also affected, Y (G2=Y).

For the second patient (D3,E3), the desired output is G3=Y (RESP affected) but F3=blank (CARDIO unaffected). For the third patient (D4, E4), neither the CARDIO or RESP systems are affected, so the desired output is that F4=blank and G4=blank

ALL DIAGNOSES LIST	CARDIO	RESP	PT#	PATIENT'S DIAGNOSIS(ES)	CARDIO	RESP
SHOCK, PULMONARY		Y	1	FIBROSIS, MYOCARDIAL; SHOCK, PULMONARY	Y	Y
FIBROSIS, MYOCARDIAL	Y		2	THROMBOSIS, PULMONARY		Y
THROMBOSIS, PULMONARY		Y	3	EDEMA, CEREBRAL

I hope I've explained my dilemma adequately and I appreciate any and all help!

Lauren.

I have tried some simple SEARCH and XLOOKUP and (IFERROR(INDEX formulas but I can't seem to find a way to return the text from a different column in the look-up list.

Answer 1

Formula in F2:

=LEFT(DROP(REDUCE(0,E2:E4,LAMBDA(t,p,VSTACK(t,REDUCE("",TEXTSPLIT(p,"; "),LAMBDA(x,y,x&XLOOKUP(y,A2:A4,B2:C4,{"",""})))))),1))

Note: LEFT() is important here since I assumed that a single diagnoses can lead to both CARDIO and RESP, and if you have multiple diagnoses that would lead to a Y in either category you'd end up with stuff like 'YY' if you don't use it.

Btw, the inner REDUCE() is there to concat all the CARDIO and RESP from each diagnosis and the outer one is there to VSTACK() the output per patient.

Answer 2

In the header row of the "PATIENT'S DIAGNOSIS(ES)" table, enter the following formula (it is entered in G1 in the example):

=LET(
    pt_diags, F2:F4,
    so_all_diags, SORT(TRIM(Sheet1!$A$2:$C$4)),
    diag_col, 1, cardio_col, 2, resp_col, 3,
    pt_diag_cat_YN, LAMBDA(acc, entry,
        LET(
            diags, TRIM(TEXTSPLIT(entry, , ";")),
            matched_diag_rows, IFERROR(
                CHOOSEROWS(so_all_diags, XMATCH(diags, INDEX(so_all_diags, , diag_col), , 2)),
                HSTACK("", "", "")
            ),
            matched_counts, HSTACK(
                SUM(--INDEX(matched_diag_rows = "Y", , cardio_col)),
                SUM(--INDEX(matched_diag_rows = "Y", , resp_col))
            ),
            VSTACK(acc, IF(matched_counts, "Y", "N"))
        )
    ),
    REDUCE({"CARDIO", "RESP"}, pt_diags, pt_diag_cat_YN)
)

Steps:

• Define input ranges: pt_diags, F2:F4, and so_all_diags, SORT(TRIM(Sheet1!$A$2:$C$4)),
  • Sorting the entries improves lookup speed
• column numbers in "ALL DIAGNOSES LIST": diag_col, 1, cardio_col, 2, resp_col, 3,
• function to get "Y/N" for each category for each entry in "PATIENT'S DIAGNOSIS(ES)":
  • pt_diag_cat_YN, LAMBDA(acc, entry,
  • split individual diagnosis by splitting: diags, TRIM(TEXTSPLIT(entry, , ";")),
  • get the matching rows in "ALL DIAGNOSES LIST", (with binary search option 2 for XMATCH): CHOOSEROWS(so_all_diags, XMATCH(diags, INDEX(so_all_diags, , diag_col), , 2))
  • in matched rows, get the counts for "Y" for catergory columns: SUM(--INDEX(matched_diag_rows = "Y", , cardio_col)), SUM(--INDEX(matched_diag_rows = "Y", , resp_col))
  • stack the results for row: VSTACK(acc, IF(matched_counts, "Y", "N"))
• apply this function to each row in "PATIENT'S DIAGNOSIS(ES)": REDUCE({"CARDIO", "RESP"}, pt_diags, pt_diag_cat_YN)

Answer 3

You could try using the following formula which uses TEXTSPLIT() as the Lookup_Value component within the XLOOKUP() function:

---

• Formula used in cell F2

=N(OR(XLOOKUP(TEXTSPLIT($E2,"; "),$A:$A,B:B,"")="Y"))

---

_{NOTE: The above formula needs to fill down and fill right, as well instead of adding another function you can use a custom format as well, as [=1]"Y";; where 1 means TRUE while FALSE which means 0 here will be hidden.}

---

Adding another function like IF() to show Y although the custom formatting does that which is actually Facade :

---

=IF(N(OR(XLOOKUP(TEXTSPLIT($E2,"; "),$A:$A,B:B,"")="Y")),"Y","")

---

Here is how one can do the custom formatting if avoiding the use one extra function like here IF():

---

Steps Are:

• Select the range of cells or columns (where in to apply the custom formatting)
• Hit CTRL+1
• Format Cells Window opens up --> From Number Tab --> Select the Category as Custom --> Under Type write the following:

---

[=1]"Y";;

---

Answer 4

updated Answer

My inlaws showed up unannounced, inspiring me to race back to my computer and improve my answer. Paste the improved makearray formula in F2

• Shorter than previous answer
• Improved calculation speed doing almost calculations the least number of times, while still being flexible for more columns.
• Allows for your y approach
• Should expand dynamically if you add more columns in the parameter (i.e. inserted a column between B and C)

=LET(PatientCol,$E:$E,sicklist,$A:$A,zSearchCols,$B:$C,
iRows,COUNTA(sicklist),zSearchClean,TAKE(zSearchCols,iRows),zSickCol,
TAKE(sicklist,iRows),
keyCol,DROP(TAKE(PatientCol,COUNTA(PatientCol)),1),
MAKEARRAY(ROWS(keyCol),COLUMNS(zSearchCols),LAMBDA(r,c,
IF(LET(patientVal,INDEX(PatientCol,r+1,1),iCol,TEXTSPLIT(patientVal,";"),
purelist,FILTER(zSickCol,INDEX(zSearchClean,,c)="Y"),colList,
BYCOL(iCol,LAMBDA(iVal,ISNUMBER(MATCH(TRIM(iVal),purelist,0)))),
REDUCE(FALSE,colList,LAMBDA(oldVal,newVal,IF(oldVal,TRUE,newVal)))),"Y",""))))

original answer

Throw this cell F2 and you should have true false values to display in all rows and both columns. If you really want to display Y and blank you can use an if-statement or something. You could probably shorten this, but it's all i got the heart for today. good luck.

=HSTACK(LET(PatientCol,$E:$E,sickList,$A:$A,testCol,B:B,BYROW(DROP(TAKE(PatientCol,
COUNTA(PatientCol)),1),LAMBDA(patientVal,LET(pureList,FILTER(sickList,testCol="Y"),
iCol,TRIM(TEXTSPLIT(patientVal,";")),colList,BYCOL(iCol,LAMBDA(iVal,
ISNUMBER(MATCH(TRIM(iVal),pureList,0)))),finalResult,REDUCE(FALSE,colList,LAMBDA(oldVal,newVal,IF(oldVal,TRUE,newVal))),finalResult)))),LET(PatientCol,$E:$E,sickList,$A:$A,testCol,C:C,BYROW(DROP(TAKE(PatientCol,
COUNTA(PatientCol)),1),LAMBDA(patientVal,LET(pureList,FILTER(sickList,testCol="Y"),
iCol,TRIM(TEXTSPLIT(patientVal,";")),colList,BYCOL(iCol,LAMBDA(iVal,
ISNUMBER(MATCH(TRIM(iVal),pureList,0)))),finalResult,REDUCE(FALSE,colList,LAMBDA(oldVal,newVal,IF(oldVal,TRUE,newVal))),finalResult)))))

Answer 5

you'll need to use a combination of functions, for the CARDIO column (Column F):

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(A$2:A$4,E2)))>0,XLOOKUP(TRUE,ISNUMBER(SEARCH(A$2:A$4,E2)),B$2:B$4,""),"")

For the RESP column (Column G):

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(A$2:A$4,E2)))>0,XLOOKUP(TRUE,ISNUMBER(SEARCH(A$2:A$4,E2)),C$2:C$4,""),"")

Enter the first formula in F2, second in G2 and copy it down for all patients.