# Find date of next change of name for roster

#### J J N

##### New Member
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

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### gaz_chops

##### Well-known Member
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

##### New Member
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

##### New Member
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

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,164,012
Messages
5,834,907
Members
430,326
Latest member
tomwax46

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back