MAX function using nested IF formula

Hi guys,

Trying to figure out a nested formula using IF and MAX functions. I've already written a formula to identify duplicates in my data set. If the PAT_NAME matches the name above AND also matches the CONTACT_DATE above then I get a 1, else I get a 0.

From here I want a second formula in column E that looks at the results of Column D, if there is a 1, then I'd like for it to look at the APPT_TIME/ADMIT_TIME column C associated with the dupe names and give me the MAX date/time from column C.

Hopefully this makes sense, but if not I've attached an example workbook. My ultimate goal is to find the very first APPT_TIME/ADMIT_TIME in column C associated with the duplicate names, since there can be several visits/appointments. I want to delete the extraneous appointments if a patient had multiple visits.

Thanks,
kdestef1

Hi, kdestef1!

If you have 0 ...

• do you want to get blank? then:

=IF(D2,MAX(INDEX((A$2:A$34=A2)*(B$2:B$34=B2)*C$2:C$34,)),"")

• do you want to get the date in C? then:

=IF(D2,MAX(INDEX((A$2:A$34=A2)*(B$2:B$34=B2)*C$2:C$34,)),C2)

Blessings!