adjusting formula get data between two dates

[abdelfattah]

hello i have this formula works based on year, i would amend between two dates the result should be in column e based on column a after writting start date and end date in range e1,g1

1 (1).xlsx
	A	B	C	D	E	F	G
1	student	date		start date		end date
2	student1	2019/01/01
3	student2	2019/03/01			student
4	student3	2019/05/01
5	student4	2019/07/01
6	student5	2019/09/01
7	student1	2019/11/01
8	student2	2020/01/01
9	student3	2020/03/01
10	student4	2020/05/01
11	student5	2020/07/01
12	student11	2020/09/01
13	student12	2020/11/01
14	student13	2021/01/01
15	student14	2021/03/01
16	student15	2021/05/01
17	student16	2021/07/01
18	student17	2021/09/01
19	student18	2021/11/01
20	student19	2022/01/01
21	student20	2022/03/01
22	student21	2022/05/01
23	student22	2022/07/01
24
Sheet1

Cell Formulas
Range	Formula
E4:E24	E4	=IFERROR(INDEX($A$2:$A$170, MATCH(0,COUNTIF($E$3:E3, IF(($E$1=YEAR($B$2:$B$170)), $A$2:$A$170, $E$3)), 0)), "")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Toadstool]

Hi AbdelFatta,

This should do what you want while avoiding an array formula.

AbdetFattah-P2.xlsx
	A	B	C	D	E	F	G
1	student	date		Start date	2/2/2020	End date	2/1/2021
2	student1	1/1/2019
3	student2	3/1/2019			student
4	student3	5/1/2019			student3
5	student4	7/1/2019			student4
6	student5	9/1/2019			student5
7	student1	11/1/2019			student11
8	student2	1/1/2020			student12
9	student3	3/1/2020			student13
10	student4	5/1/2020
11	student5	7/1/2020
12	student11	9/1/2020
13	student12	11/1/2020
14	student13	1/1/2021
15	student14	3/1/2021
16	student15	5/1/2021
17	student16	7/1/2021
18	student17	9/1/2021
19	student18	11/1/2021
20	student19	1/1/2022
21	student20	3/1/2022
22	student21	5/1/2022
23	student22	7/1/2022
Sheet1

Cell Formulas
Range	Formula
E4:E23	E4	=IFERROR(INDEX($A$2:$A$23,AGGREGATE(15,6,ROW($A$2:$A$23)-ROW($A$1)/(($B$2:$B$23>=$E$1)*($B$2:$B$23<=$G$1)),ROW()-ROW($E$3))),"")

 

[abdelfattah]

many thanks it works very well

 

[abdelfattah]

hi, toad i would amend the formula if you don't mind i want duplicated data highlighted by yellow color should deleted when search by two dates

 

[Toadstool]

You can extract into an unused column (maybe hide it?) then de-duplicate into your target area:

AbdetFattah-P2.xlsx
	A	B	C	D	E	F	G	H
1	student	date		Start date	2019/1/1	End date	2020/5/1
2	student1	2019/1/1
3	student2	2019/3/1			student			Extract
4	student3	2019/5/1			student1			student1
5	student4	2019/7/1			student2			student2
6	student5	2019/9/1			student3			student3
7	student1	2019/11/1			student4			student4
8	student2	2020/1/1			student5			student5
9	student3	2020/3/1						student1
10	student4	2020/5/1						student2
11	student5	2020/7/1						student3
12	student11	2020/9/1						student4
13	student12	2020/11/1
14	student13	2021/1/1
15	student14	2021/3/1
16	student15	2021/5/1
17	student16	2021/7/1
18	student17	2021/9/1
19	student18	2021/11/1
20	student19	2022/1/1
21	student20	2022/3/1
22	student21	2022/5/1
23	student22	2022/7/1
v2 (2)

Cell Formulas
Range	Formula
E4:E23	E4	=IFERROR(INDEX($H$4:$H$23,MATCH(0,INDEX(COUNTIF($E$3:$E3,$H$4:$H$23),),0))&"","")
H4:H23	H4	=IFERROR(INDEX($A$2:$A$23,AGGREGATE(15,6,ROW($A$2:$A$23)-ROW($A$1)/(($B$2:$B$23>=$E$1)*($B$2:$B$23<=$G$1)),ROW()-ROW($H$3))),"")

 

[abdelfattah]

hi, toad it works but i prefer do that without in depend on specific column like h as in this case if is way directly

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G
1	student	date		start date	01/01/2019	end date	31/12/2020
2	student1	01/01/2019
3	student2	03/01/2019			student
4	student3	05/01/2019			student1
5	student4	07/01/2019			student2
6	student5	09/01/2019			student3
7	student1	11/01/2019			student4
8	student2	01/01/2020			student5
9	student3	03/01/2020			student11
10	student4	05/01/2020			student12
11	student5	07/01/2020
12	student11	09/01/2020
13	student12	11/01/2020
14	student13	01/01/2021
15	student14	03/01/2021
16	student15	05/01/2021
17	student16	07/01/2021
18	student17	09/01/2021
19	student18	11/01/2021
20	student19	01/01/2022
21	student20	03/01/2022
22	student21	05/01/2022
23	student22	07/01/2022
24
25
Main

Cell Formulas
Range	Formula
E4:E24	E4	=IFERROR(INDEX($A$2:$A$170, MATCH(0,COUNTIF($E$3:E3, $A$2:$A$170)+($B$2:$B$170<$E$1)+($B$2:$B$170>$G$1), 0)), "")

 

[abdelfattah]

it gives me empty no data

 

[Fluff]

Can you please post your data, with the formula.

 

[abdelfattah]

إ (1).xlsx
	A	B	C	D	E	F	G
1	student	date		start date	01/01/2019	end date	05/01/2020
2	student1	01/01/2019
3	student2	03/01/2019			student
4	student3	05/01/2019
5	student4	07/01/2019
6	student5	09/01/2019
7	student1	11/01/2019
8	student2	01/01/2020
9	student3	03/01/2020
10	student4	05/01/2020
11	student5	07/01/2020
12	student11	09/01/2020
13	student12	11/01/2020
14	student13	01/01/2021
15	student14	03/01/2021
16	student15	05/01/2021
17	student16	07/01/2021
18	student17	09/01/2021
19	student18	11/01/2021
20	student19	01/01/2022
21	student20	03/01/2022
22	student21	05/01/2022
23	student22	07/01/2022
24
25
26
Sheet1

Cell Formulas
Range	Formula
E4:E24	E4	=IFERROR(INDEX($A$2:$A$170,MATCH(0,COUNTIF($E$3:E3, $A$2:$A$170)+($B$2:$B$170<$E$1)+($B$2:$B$170>$G$1), 0)), "")