Fill End Date with Start date of the next Matching Entry

Hello,
New to posting to the forums, I have come here for assistance with other Excel issues and have had good luck in searching to find the results. I cannot find a way to do what I need with the attached worksheet (and it may be due to me searching for the wrong wording for what I need)

In the attached workbook I need a formula that will auto fill the END Date (E) based on the next start date that matches Name (A) AND Region (B).

ExcelDateHelpWorkbook.xlsx

If the wording I am using is incorrect please let me know what I need to search by to figure this out.

Thanks for any assistance given.

welcome to the forum, Whraith. so a combination of John & France would only appear twice & you want the end date of the 1st instance to be filled with the start date of the 2nd instance? try this in E2:
=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,LOOKUP(2,1/(($A$2:$A$10=A2)*($B$2:$B$10=B2)),$D$2:$D$10),"")

format to date

Thank you for the welcome and fast response.

Originally Posted by benishiryo

so a combination of John & France would only appear twice & you want the end date of the 1st instance to be filled with the start date of the 2nd instance?

John & France can appear multiple times I only put two in the example I should have included more. Essentially after every job is finished and a new one is entered and I would need it to fill in the date (end date of the older one) with the start date of the new entry.

The formula you gave only updates the first instance of the Match of John & France.

Name Region Start End
John France 11/1/2013 11/11/2013
John France 11/11/2013 11/15/2013
John England 11/6/2013 11/7/2013
John England 11/7/2013
Frank Germany 11/7/2013
John France 11/15/2013

maybe any of these array formulas:
=IFERROR(INDEX(D3:D$10,MATCH(A2&B2,A3:A$10&B3:B$10,0)),"")

or:

Formula:

Please Login or Register  to view this content.

...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. Press F2 on that cell and try again.