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.

ID	Appt Date	Ref_Date	Referral Number	First Appt Date	Last Appt Date
U0110951	15/02/2016	05/03/2014	1	04/04/2016	15/02/2016
U0110951	04/04/2016	05/03/2014	1	04/04/2016	15/02/2016
U0110951	24/09/2015	07/07/2015	2	24/09/2015	19/10/2015
U0110951	19/10/2015	07/07/2015	2	24/09/2015	19/10/2015
U0110951	10/05/2016	11/04/2016	3	10/05/2016	10/05/2016
U0110951	07/09/2016	19/08/2016	4	07/09/2016	14/07/2017
U0110951	21/09/2016	19/08/2016	4	07/09/2016	14/07/2017
U0110951	21/10/2016	19/08/2016	4	07/09/2016	14/07/2017
U0110951	23/11/2016	19/08/2016	4	07/09/2016	14/07/2017
U0110951	08/02/2017	19/08/2016	4	07/09/2016	14/07/2017
U0110951	22/03/2017	19/08/2016	4	07/09/2016	14/07/2017
U0110951	29/03/2017	19/08/2016	4	07/09/2016	14/07/2017
U0110951	09/05/2017	19/08/2016	4	07/09/2016	14/07/2017
U0110951	31/05/2017	19/08/2016	4	07/09/2016	14/07/2017
U0110951	15/06/2017	19/08/2016	4	07/09/2016	14/07/2017
U0110951	14/07/2017	19/08/2016	4	07/09/2016	14/07/2017

<tbody>
</tbody>

 

[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)))