Comments to be picked to another table with start and end dates as range

[BabuV]

Hi,

I have 2 Tables.

Table 1 :

Start Date	End Date	Comments
13-Aug-20​	16-Aug-20​	13-Aug'20 to 16-Aug'20
17-Aug-20​	23-Aug-20​	17-Aug'20 to 23-Aug'20
24-Aug-20​	28-Aug-20​	24-Aug'20 to 28-Aug'20
29-Aug-20​	31-Aug-20​	29-Aug'20 to 31-Aug'20
1-Sep-20​	15-Sep-20​	01-Sep'20 to 15-Sep'20
16-Sep-20​	30-Sep-20​	16-Sep'20 to 30-Sep'20
1-Oct-20​	15-Oct-20​	01-Oct'20 to 15-Oct'20
16-Oct-20​	31-Oct-20​	16-Oct'20 to 31-Oct'20

I need comments to be populated in below table 2nd column with respect to start and end date range.

Submitted date	Comments
16-Oct-20​
23-Aug-20​
7-Sep-20​
9-Oct-20​
31-Oct-20​

CAn someone please help with the formula?

 

[CA_Punit]

Try This

Book1
	A	B	C	D
2	Start Date	End Date	Comment
3	13/08/2020	16/08/2020	a
4	17/08/2020	23/08/2020	b
5	24/08/2020	28/08/2020	c
6	29/08/2020	31/08/2020	d
7	01/09/2020	15/09/2020	e
8	16/09/2020	30/09/2020	f
9	01/10/2020	15/10/2020	g
10	16/10/2020	31/10/2020	h
11
12
13	Submitted Date
14	16/10/2020		h
15	23/08/2020		b
16	07/09/2020		e
17	09/10/2020		g
18	31/10/2020		h
19
20
Sheet5

Cell Formulas
Range	Formula
C14:C18	C14	=LOOKUP(A14,$A$3:$A$10,$C$3:$C$9)

 

[ER_Neha]

Hello you have to customize the date format if you are going to use this format for your formula.
for that ->format cell->custom->select dd-mmm-yy format
then you can try this formula

Book1
	A	B	C	D	E	F
1	Start Date	End Date	Comments		SUBMITTED DATE	Comments
2	13-Aug-20	16-Aug-20	13-Aug'20 to 16-Aug'20		16-Oct-20	16-Oct'20 to 31-Oct'20
3	17-Aug-20	23-Aug-20	17-Aug'20 to 23-Aug'20		23-Aug-20	17-Aug'20 to 23-Aug'20
4	24-Aug-20	28-Aug-20	24-Aug'20 to 28-Aug'20		07-Sep-20	01-Sep'20 to 15-Sep'20
5	29-Aug-20	31-Aug-20	29-Aug'20 to 31-Aug'20		09-Oct-20	01-Oct'20 to 15-Oct'20
6	01-Sep-20	15-Sep-20	01-Sep'20 to 15-Sep'20		31-Oct-20	16-Oct'20 to 31-Oct'20
7	16-Sep-20	30-Sep-20	16-Sep'20 to 30-Sep'20
8	01-Oct-20	15-Oct-20	01-Oct'20 to 15-Oct'20
9	16-Oct-20	31-Oct-20	16-Oct'20 to 31-Oct'20
10
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=INDEX($C$2:$C$9,SUMPRODUCT(($E2>=$A$2:$A$9)*($E2<=$B$2:$B$9)*MATCH(ROW($C$2:$C$9),ROW($C$2:$C$9))))