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

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

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.

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";;

---