# adjusting formula get data between two dates

#### abdelfattah

##### Well-known Member
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
ABCDEFG
1studentdatestart dateend date
2student12019/01/01
3student22019/03/01student
4student32019/05/01
5student42019/07/01
6student52019/09/01
7student12019/11/01
8student22020/01/01
9student32020/03/01
10student42020/05/01
11student52020/07/01
12student112020/09/01
13student122020/11/01
14student132021/01/01
15student142021/03/01
16student152021/05/01
17student162021/07/01
18student172021/09/01
19student182021/11/01
20student192022/01/01
21student202022/03/01
22student212022/05/01
23student222022/07/01
24
Sheet1
Cell Formulas
RangeFormula
E4:E24E4=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.

##### Well-known Member
...
=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)), "")
...

@Fluff It's very possible I'm missing something but wouldn't this need to be entered as an array formula using CSE?

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Fluff

##### MrExcel MVP, Moderator
Oops, keep forgetting to do that.
@abdelfattah
As mentioned by Toadstool, the formula should be confirmed with Ctrl Shift Enter, not just enter.

#### abdelfattah

##### Well-known Member
thanks fluff now it works very well

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
7
Views
156
Replies
2
Views
109
Replies
3
Views
106
Replies
17
Views
612
Replies
2
Views
68