Find date of next change of name for roster

[J J N]

Hi All,
New here, I have scoured these forums and others trying to get an answer to my task.
I have a SS that has a date(col a),name(col b) and contact number(col c) that we use as a reference to a roster.
I can extract the name and number based on the date , what I am hoping to do is show the date when the roster changes.
In the example below Fred is on roster today, 20/07/2020, and Dan is to start on 22/07/2020.
On the main roster I want to show that Fred is on roster until 21/07/2020 when Dan takes over.
I have looked at match and index functions but can't get them

20/07/2020​	Fred Bloggs	0400 000 111
21/07/2020​	Fred Bloggs	0400 000 111
22/07/2020​	Dan Dare	0400 111 111
23/07/2020​	Dan Dare	0400 111 111
24/07/2020​	Dan Dare	0400 111 111
25/07/2020​	Dan Dare	0400 111 111
26/07/2020​	Dan Dare	0400 111 111
27/07/2020​	Dan Dare	0400 111 111
28/07/2020​	Dan Dare	0400 111 111
29/07/2020​	Stan Smith	0400 222 111
30/07/2020​	Stan Smith	0400 222 111
31/07/2020​	Stan Smith	0400 222 111
1/08/2020​	Stan Smith	0400 222 111
2/08/2020​	Stan Smith	0400 222 111

 

[gaz_chops]

Welcome to the forum, try

=AGGREGATE(14,6,($A$1:$A$14)/($B$1:$B$14=H3),1)

Change H3 to the cell on Main Roster which contains the Name.

 

[J J N]

hi gaz, i tried this but get the dreaded #NUM! , i found help on the aggregate function but cannot understand how what ,($A$1:$A$14)/($B$1:$B$14=H3) is trying to achieve,
is it A1:A14 divided by B1:B14 equals Name (Fred Bloggs ) , and the 1 indicates give me the highest return value ?
Thanks
J

 

[J J N]

an update gaz, i put the formula into a copy of my lice spreadsheet, and it returns a numeric value 44196, can you shed any light ?

 

[gaz_chops]

44196 is the date, simply re-format as date and it should be ok.