Choosing a drug for a patient ( according to contraindications)

Question

Can anyone please help me in choosing the suitable drug

the case: patient A is suffering from

• hypertension & Kidney disease

Drugs:

• Classified according the medical use:

e.g., { class A } : For painkillers but as you see class A contain 3 drugs , I want a formula which will choose the suitable drug ( number 3 ) and not 1 or 2 or any drug from other classifications.

I tried to use IF function after naming true , false cells

IF(OR(AND(hypertension=TRUE,D4=1),AND(kidneydisease=TRUE,C4=1),AND(liverdisease=TRUE,B4=1)),b5,b4)

I think this helps me to make sure that the drug is suitable , but if it isn't the one , how the formula should test the next one.

thank you.

Answer 1

From the comments it is apparent that only someone with the requisite clinical knowledge can determine the class of drugs to be considered; accordingly the suggestion below uses a dropdown in cell I12 for the appropriately-qualified user to make this choice: As your version of Excel is unknown this proposal uses a helper column, coupled with conditional formatting (in order not to have an overly-long formula). The array formula in cells K6:K10 is

--NOT(MMULT($G$6:$I$10,--$D$7:$D$9))*($J$6:$J$10=I12)

which returns either a 1 if the drug on that row is suitable, or a 0 otherwise. Conditional formatting is then applied to the F6:J10 range to highlight the suitable drug(s): (to implement the conditional-formatting rule, on the Home tab of the ribbon, click on Conditional Formatting -> New Rule, and you will be presented with a window similar to the above (click on Format to define your highlight style))