CODEWITHSUNDEEP
if-statementindexingexcel-formulamatch
n8-da-gr8
n8-da-gr8

Reputation: 551

Combine two nested IF statements with multiple criteria

I have two columns of data in "Meds" sheet... 

  MedContinuing     AgeAtMedStop
      Yes             "Blank"
      Yes              72.22
      No              "Blank"
      No               72.57
    "Blank"            73.85

I am writing a formula in a separate sheet to return 1 or 0 based on the following:

  1. If MedContinuing is "Blank", do nothing
  2. If MedContinuing is "No" and AgeAtMedStop is blank, do nothing
  3. If MedContinuing is "Yes" and AgeAtMedStop is "Blank", return 1. If AgeAtMedStop is a number, return 0.
  4. If MedContinuing is "No" and AgeAtMedStop is a number, return 1. Otherwise, return nothing.

I was able to write two separate functions (see below) for when MedContinuing is "Yes" or when it is "No", but I need to combine both into one formula.

When it's Yes...

=IF(INDEX(Meds!2:2,MATCH("MedContinuing",Meds!$1:$1,0))="","",
IF(INDEX(Meds!2:2,MATCH("MedContinuing",Meds!$1:$1,0))="No","",
IF(AND(INDEX(Meds!2:2,MATCH("MedContinuing",Meds!$1:$1,0))="Yes",INDEX(Meds!2:2,MATCH("AgeAtMedStop",Meds!$1:$1,0))=""),1,0)))

When it's No...

=IF(INDEX(Meds!2:2,MATCH("MedContinuing",Meds!$1:$1,0))="","",
IF(INDEX(Meds!2:2,MATCH("MedContinuing",Meds!$1:$1,0))="Yes","",
IF(AND(INDEX(Meds!2:2,MATCH("MedContinuing",Meds!$1:$1,0))="No",INDEX(Meds!2:2,MATCH("AgeAtMedStop",Meds!$1:$1,0))=""),"",
IF(AND(INDEX(Meds!2:2,MATCH("MedContinuing",Meds!$1:$1,0))="No",INDEX(Meds!2:2,MATCH("AgeAtMedStop",Meds!$1:$1,0))>0),1,0))))

EDIT: Solution

Using Peter K's logic...

=IF(INDEX(Meds!6:6,MATCH("MedContinuing",Meds!$1:$1,0))="","",
IF(AND(INDEX(Meds!6:6,MATCH("MedContinuing",Meds!$1:$1,0))="No",INDEX(Meds!6:6,MATCH("AgeAtMedStop",Meds!$1:$1,0))=""),"",
IF(AND(INDEX(Meds!6:6,MATCH("MedContinuing",Meds!$1:$1,0))="Yes",INDEX(Meds!6:6,MATCH("AgeAtMedStop",Meds!$1:$1,0))=""),1,
IF(AND(INDEX(Meds!6:6,MATCH("MedContinuing",Meds!$1:$1,0))="Yes",INDEX(Meds!6:6,MATCH("AgeAtMedStop",Meds!$1:$1,0))>0),0,
IF(AND(INDEX(Meds!6:6,MATCH("MedContinuing",Meds!$1:$1,0))="No",INDEX(Meds!6:6,MATCH("AgeAtMedStop",Meds!$1:$1,0))>0),1,"")))))

Upvotes: 0

Views: 558

Answers (2)

usmanhaq
usmanhaq

Reputation: 1577

You can try this method below

I have created a helper table for the logic you require, it will help to update or extend the logic in future

Formula in cell C2 is 

=INDEX($F$2:$G$4,MATCH(A2,$E$2:$E$4,0),IF(B2="Blank",1,IF(ISNUMBER(B2),2,0)))

enter image description here

Upvotes: 0

Peter K.
Peter K.

Reputation: 960

It is not entirely clear from your question why you would use INDEX and MATCH functions for such straightforward problem ?

I suggest to start with the basic nested if function :
=IF(A2="";"";IF(A2="No";IF(B2="";"";1);IF(B2="";1;0)))

You can put this function next to your two columns, and then copy to another worksheet, so the references are taken care of by Excel. I also assume that your data is clean and correct i.e. only the 3 possible values for MedContinuing ("Yes", "No" or blank) and 2 for AgeAtMedStop (blank or a number) exist in your columns, so no IF test is needed to eliminate other possible values.

Upvotes: 2

Related Questions