Excel Table - Look Up ID and Return Date Column of Next Row

[pquser]

Hi Everyone - I have an Excel table as shown in the attached image. I want to add a column called Date End that looks at the ID and if the next row has the same ID, return the Date Start value of that next row. Otherwise if there is not another row with the same ID, return null. The image shows what my desired result is in red. Please let me know if you need anything else to help solve this!

 

[AhoyNC]

Try:

Book2
	A	B	C	D
1	ID	Start		End
2	100001	7/30/2018		null
3	100002	1/1/2018		null
4	100005	9/5/2017		3/30/2020
5	100005	3/30/2020		4/1/2021
6	100005	4/1/2021		null
7	100008	9/2/2014		4/1/2020
8	100008	4/1/2020		2/15/2021
9	100008	2/15/2021		null
10	100013	1/1/2018		null
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=IF(A2=A3,B3,"null")

 

[pquser]

Try:

Book2
	A	B	C	D
1	ID	Start		End
2	100001	7/30/2018		null
3	100002	1/1/2018		null
4	100005	9/5/2017		3/30/2020
5	100005	3/30/2020		4/1/2021
6	100005	4/1/2021		null
7	100008	9/2/2014		4/1/2020
8	100008	4/1/2020		2/15/2021
9	100008	2/15/2021		null
10	100013	1/1/2018		null
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=IF(A2=A3,B3,"null")

Thanks this does work. Is there a way to do this without cell references but with INDEX MATCH?

 

[Peter_SSs]

Is there a way to do this without cell references but with INDEX MATCH?

Any particular reason to look for a more complex formula?

 

[AhoyNC]

Not sure what you mean by "Is there a way to do this without cell references".
Here is one way to use INDEX/MATCH or XLOOKUP.

Book1
	A	B	C	D	E	F	G	H
1	ID	Start		End		INDEX/MATCH		XLOOKUP
2	100001	7/30/2018		null		null		null
3	100002	1/1/2018		null		null		null
4	100005	9/5/2017		3/30/2020		3/30/2020		3/30/2020
5	100005	3/30/2020		4/1/2021		4/1/2021		4/1/2021
6	100005	4/1/2021		null		null		null
7	100008	9/2/2014		4/1/2020		4/1/2020		4/1/2020
8	100008	4/1/2020		2/15/2021		2/15/2021		2/15/2021
9	100008	2/15/2021		null		null		null
10	100013	1/1/2018		null		null		null
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=IF(A2=A3,B3,"null")
F2:F10	F2	=IFERROR(INDEX(B3:B10,MATCH(A2,A3:A10,0)),"null")
H2:H10	H2	=XLOOKUP(A2,A3,B3,"null")