A solution using two separate tables, AllDiagList and PtDiag:

• assuming (patient) diagnoses are separated by ;

Then in table PtDiag, enter in the first cells

• CARDIO

=LET(pt_diags, TRIM(TEXTSPLIT([@[PATIENT''S DIAGNOSIS(ES)]], ";")),
IF(SUM(ISNUMBER(XMATCH(AllDiagList[Diag], pt_diags)) * (AllDiagList[CARDIO] = "Y")), "Y", "N"))

• RESP

=LET(pt_diags, TRIM(TEXTSPLIT([@[PATIENT''S DIAGNOSIS(ES)]], ";")),
IF(SUM(ISNUMBER(XMATCH(AllDiagList[Diag], pt_diags)) * (AllDiagList[RESP] = "Y")), "Y", "N"))

Please let me know if you need further explanation of the steps.