Max and Min Dates multiple criteria

[jingamells]

I am trying to populate two columns with min and max dates based on multiple criteria matching (ID and Ref number).
I could probably do a multiple IF but sure there is a better way? B
elow is an extract of data. I am trying to populate the last two columns (first and last Appt date) with the values shown from the Appt date column when ID and ref number are the same

Many thanks in advance.

[TABLE="width: 522"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Appt Date
[/TD]
[TD]Ref_Date
[/TD]
[TD]Referral Number
[/TD]
[TD]First Appt Date
[/TD]
[TD]Last Appt Date
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]15/02/2016
[/TD]
[TD="align: right"]05/03/2014
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]04/04/2016
[/TD]
[TD="align: right"]15/02/2016
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]04/04/2016
[/TD]
[TD="align: right"]05/03/2014
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]04/04/2016
[/TD]
[TD="align: right"]15/02/2016
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]24/09/2015
[/TD]
[TD="align: right"]07/07/2015
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]24/09/2015
[/TD]
[TD="align: right"]19/10/2015
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]19/10/2015
[/TD]
[TD="align: right"]07/07/2015
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]24/09/2015
[/TD]
[TD="align: right"]19/10/2015
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]10/05/2016
[/TD]
[TD="align: right"]11/04/2016
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]10/05/2016
[/TD]
[TD="align: right"]10/05/2016
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]07/09/2016
[/TD]
[TD="align: right"]19/08/2016
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]07/09/2016
[/TD]
[TD="align: right"]14/07/2017
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]21/09/2016
[/TD]
[TD="align: right"]19/08/2016
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]07/09/2016
[/TD]
[TD="align: right"]14/07/2017
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]21/10/2016
[/TD]
[TD="align: right"]19/08/2016
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]07/09/2016
[/TD]
[TD="align: right"]14/07/2017
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]23/11/2016
[/TD]
[TD="align: right"]19/08/2016
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]07/09/2016
[/TD]
[TD="align: right"]14/07/2017
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]08/02/2017
[/TD]
[TD="align: right"]19/08/2016
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]07/09/2016
[/TD]
[TD="align: right"]14/07/2017
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]22/03/2017
[/TD]
[TD="align: right"]19/08/2016
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]07/09/2016
[/TD]
[TD="align: right"]14/07/2017
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]29/03/2017
[/TD]
[TD="align: right"]19/08/2016
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]07/09/2016
[/TD]
[TD="align: right"]14/07/2017
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]09/05/2017
[/TD]
[TD="align: right"]19/08/2016
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]07/09/2016
[/TD]
[TD="align: right"]14/07/2017
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]31/05/2017
[/TD]
[TD="align: right"]19/08/2016
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]07/09/2016
[/TD]
[TD="align: right"]14/07/2017
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]15/06/2017
[/TD]
[TD="align: right"]19/08/2016
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]07/09/2016
[/TD]
[TD="align: right"]14/07/2017
[/TD]
[/TR]
[TR]
[TD]U0110951
[/TD]
[TD="align: right"]14/07/2017
[/TD]
[TD="align: right"]19/08/2016
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]07/09/2016
[/TD]
[TD="align: right"]14/07/2017
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

1. If you have MINIFS, in E2 just enter and copy down:

=MINIFS($B$2:$B$17,$A$2:$A$17,$A2,$D2:$D$17,$D2)

Otherwise, in E2 control+shift+enter, not just enter, and copy down

=MIN(IF($A$2:$A$17=$A2,IF($D$2:$D$17=$D2,$B$2:$B$17)))

2. If you have MAXIFS, in F2 just enter and copy down:

=MAXIFS($B$2:$B$17,$A$2:$A$17,$A2,$D2:$D$17,$D2)

Otherwise, in F2 control+shift+enter, not just enter, and copy down

=MAX(IF($A$2:$A$17=$A2,IF($D$2:$D$17=$D2,$B$2:$B$17)))