Date Range based on Cell Value

[Andy0311]

Hello,

I have a report (shown below) for auditing pay periods and the date a lesson was graded. In column D the first entry is 9/19/2019 (in red). Based on a date in column D, I need to key in the appropriate start date (column F) and end date (column G) for the value in column D. This is presently a manual process where someone looks at the value in column D, and looks in column K and column L to find the correct date range. The 9/19/2019 date falls between the 9/10/2019 to 9/24/2019 date range (see blue dates in columns K and L. I would like to automate the process so that a formula in column F and G would eliminate the keying by calculating the proper dates. I've tried IF statements, but can't get it right. Any help would be appreciated. Sorry the formatting in my example is sub standard. A

[TABLE="width: 1198"]
<colgroup><col><col><col><col><col><col><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]Student Name[/TD]
[TD]Course[/TD]
[TD]Lesson[/TD]
[TD]Date Graded[/TD]
[TD]Mark[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pay Periods

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Katz[/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]Unit 18: First Semester Final[/TD]
[TD]9/19/2019[/TD]
[TD]B [/TD]
[TD="align: right"]9/10 [/TD]
[TD]9/24[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]Katz[/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]Unit 18: First Semester Final[/TD]
[TD]9/27/2019[/TD]
[TD]B+[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8/10/2019[/TD]
[TD]8/24/2019[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8/25/2019[/TD]
[TD]9/9/2019[/TD]
[/TR]
[TR]
[TD]Jones[/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]Unit 6: Functions[/TD]
[TD]9/17/2019[/TD]
[TD]D-[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9/10/2019[/TD]
[TD]9/24/2019[/TD]
[/TR]
[TR]
[TD]Jones[/TD]
[TD]Math Integrated Math III (1-18)[/TD]
[TD]Unit 6: Functions[/TD]
[TD]9/27/2019[/TD]
[TD]D[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9/25/2019[/TD]
[TD]10/9/2019[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2019[/TD]
[TD]10/24/2019[/TD]
[/TR]
[TR]
[TD]Bonny[/TD]
[TD]MATH 170 (1-18)[/TD]
[TD]Unit 5: Exponents, Square Roots and Place Values[/TD]
[TD]9/12/2019[/TD]
[TD]F[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Bonny[/TD]
[TD]Math 170 (1-18)[/TD]
[TD]Unit 5: Exponents, Square Roots and Place Values[/TD]
[TD]9/26/2019[/TD]
[TD]F[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Student Name	Course	Lesson	Date Graded	Mark	Start	End				Pay Periods
2	Katz	Math Integrated Math III (1-18)	Unit 18: First Semester Final	19/09/2019	B	10-Sep	24-Sep				Start	End
3	Katz	Math Integrated Math III (1-18)	Unit 18: First Semester Final	27/09/2019	B+	25-Sep	09-Oct				10/08/2019	24/08/2019
4											25/08/2019	09/09/2019
5	Jones	Math Integrated Math III (1-18)	Unit 6: Functions	17/09/2019	D-	10-Sep	24-Sep				10/09/2019	24/09/2019
6	Jones	Math Integrated Math III (1-18)	Unit 6: Functions	27/09/2019	D	25-Sep	09-Oct				25/09/2019	09/10/2019
7											10/10/2019	24/10/2019
8	Bonny	MATH 170 (1-18)	Unit 5: Exponents, Square Roots and Place Values	12/09/2019	F	10-Sep	24-Sep
9	Bonny	Math 170 (1-18)	Unit 5: Exponents, Square Roots and Place Values	26/09/2019	F	25-Sep	09-Oct
Specs

Cell Formulas
Range	Formula
F2	=IFERROR(INDEX(K$3:K$7,MATCH($D2,K$3:K$7,1)),"")
G2	=IFERROR(INDEX(L$3:L$7,MATCH($F2,$K$3:$K$7,1)),"")

 

[Andy0311]

How about

	A	B	C	D	E	F	G	H	I	J	K	L
Student Name	Course	Lesson	Date Graded	Mark	Start	End	Pay Periods
Katz	Math Integrated Math III (1-18)	Unit 18: First Semester Final	B	Start	End
Katz	Math Integrated Math III (1-18)	Unit 18: First Semester Final	B+
Jones	Math Integrated Math III (1-18)	Unit 6: Functions	D-
Jones	Math Integrated Math III (1-18)	Unit 6: Functions	D
Bonny	MATH 170 (1-18)	Unit 5: Exponents, Square Roots and Place Values	F
Bonny	Math 170 (1-18)	Unit 5: Exponents, Square Roots and Place Values	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]19/09/2019[/TD]

[TD="align: right"]10-Sep[/TD]
[TD="align: right"]24-Sep[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]27/09/2019[/TD]

[TD="align: right"]25-Sep[/TD]
[TD="align: right"]09-Oct[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10/08/2019[/TD]
[TD="align: right"]24/08/2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25/08/2019[/TD]
[TD="align: right"]09/09/2019[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]17/09/2019[/TD]

[TD="align: right"]10-Sep[/TD]
[TD="align: right"]24-Sep[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10/09/2019[/TD]
[TD="align: right"]24/09/2019[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]27/09/2019[/TD]

[TD="align: right"]25-Sep[/TD]
[TD="align: right"]09-Oct[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25/09/2019[/TD]
[TD="align: right"]09/10/2019[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10/10/2019[/TD]
[TD="align: right"]24/10/2019[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]12/09/2019[/TD]

[TD="align: right"]10-Sep[/TD]
[TD="align: right"]24-Sep[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]26/09/2019[/TD]

[TD="align: right"]25-Sep[/TD]
[TD="align: right"]09-Oct[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Specs​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IFERROR(INDEX(K$3:K$7,MATCH($D2,K$3:K$7,1)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IFERROR(INDEX(L$3:L$7,MATCH($F2,$K$3:$K$7,1)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you so much for your help on this, and the other times you have helped me. This solution was perfect and way beyond my scope of Excel knowledge, and saved me hours in time and frustration. Thanks, Fluff!

Andy

 

[Fluff]

Glad to help & thanks for the feedback