# MAX function using nested IF formula

1. ## 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  Register To Reply

2. ## Re: MAX function using nested IF formula

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!  Register To Reply

3. ## Re: MAX function using nested IF formula Originally Posted by kdestef1 ...My ultimate goal is to find the very first APPT_TIME/ADMIT_TIME in column C associated with the duplicate names...
If you want the FIRST appt, shouldnt you be looking for the LOWEST (1st) date, not the LARGEST (last/max) date?

If so, then I think this ARRAY formula does what you want, with no need for col D...
=IFERROR(SMALL(IF((A2&B2=A1&B1)*(\$A\$2:\$A\$34&\$B\$2:\$B\$34=A3&B3),\$C\$2:\$C\$34),1),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Then copy down and format as needed  Register To Reply