Index match to specific month

[Sumeluar]

Folks, what am I doing wrong?
I have a table which contains two columns "Date" (Column EJ) & "Description" (Collumn EK), I want to use Index to pick all the events form April but I cannot get the formula to work. Here's what I have: =INDEX('Perpetual Calendar'!$EK$8:$EK$200,IF((DATE(2021,4,1)='Perpetual Calendar'!$EJ$8:$EJ$200*DATE(2021,5,1)<='Perpetual Calendar'!$EJ$8:$EJ$200),MATCH(ROW('Perpetual Calendar'!$EK$8:$EK$200),ROW('Perpetual Calendar'!$EK$8:$EK$200)),""),ROWS($A$1:$A1)). The result I'm getting is: #Value!

Please guide me in the right direction, thanks in advance.

 

[Rino702]

Try the following formula:
=IF((DATE(2021,4,1)<='Perpetual Calendar'!$EJ$8:$EJ$200)*(DATE(2021,5,1)>='Perpetual Calendar'!$EJ$8:$EJ$200),'Perpetual Calendar'!$EK$8:$EK$200,"")

 

[Sumeluar]

Try the following formula:
=IF((DATE(2021,4,1)<='Perpetual Calendar'!$EJ$8:$EJ$200)*(DATE(2021,5,1)>='Perpetual Calendar'!$EJ$8:$EJ$200),'Perpetual Calendar'!$EK$8:$EK$200,"")

Thanks Rino, is almost there!

The problem is that April starts 73 rows from the top down, the formula throws 73 empty rows before it shows all the activities for that month.

 

[Rino702]

Hi,
If you are office 365 you use LET function like this:

=LET(x, IF((DATE(2021,4,1)<='Perpetual Calendar'!$EJ$8:$EJ$200)*(DATE(2021,5,1)>='Perpetual Calendar'!$EJ$8:$EJ$200),'Perpetual Calendar'!$EK$8:$EK$200,""), FILTER(x, x<>""))

Regards

 

[Eric W]

Or for older versions of Excel:

Book2
	EJ	EK	EL	EM	EN
7	Date	Description		April Events
8	1-Jan	Event1		Event4	Event4
9	1-Feb	Event2		Event5	Event5
10	1-Mar	Event3		Event6	Event6
11	1-Apr	Event4		Event7	Event7
12	3-Apr	Event5		Event11	Event11
13	4-Apr	Event6
14	19-Apr	Event7
15	1-May	Event8
16	1-Jun	Event9
17	31-Jul	Event10
18	22-Apr	Event11
19
Perpetual Calendar

Cell Formulas
Range	Formula
EN8:EN12	EN8	=FILTER('Perpetual Calendar'!EK8:EK200,(MONTH('Perpetual Calendar'!EJ8:EJ200)=4)*(YEAR('Perpetual Calendar'!EJ8:EJ200)=2021))
EM8:EM18	EM8	=IFERROR(INDEX('Perpetual Calendar'!EK:EK,AGGREGATE(15,6,ROW('Perpetual Calendar'!EJ$8:EJ$200)/(4=MONTH('Perpetual Calendar'!EJ$8:EJ$200))/(2021=YEAR('Perpetual Calendar'!EJ$8:EJ$200)),ROWS(EM$8:EM8))),"")
Dynamic array formulas.

 

[Sumeluar]

Hi,
If you are office 365 you use LET function like this:

=LET(x, IF((DATE(2021,4,1)<='Perpetual Calendar'!$EJ$8:$EJ$200)*(DATE(2021,5,1)>='Perpetual Calendar'!$EJ$8:$EJ$200),'Perpetual Calendar'!$EK$8:$EK$200,""), FILTER(x, x<>""))

Regards

Rino, thanks again. That formula worked great!

Appreciate the response.

 

[Sumeluar]

Or for older versions of Excel:

Book2
	EJ	EK	EL	EM	EN
7	Date	Description		April Events
8	1-Jan	Event1		Event4	Event4
9	1-Feb	Event2		Event5	Event5
10	1-Mar	Event3		Event6	Event6
11	1-Apr	Event4		Event7	Event7
12	3-Apr	Event5		Event11	Event11
13	4-Apr	Event6
14	19-Apr	Event7
15	1-May	Event8
16	1-Jun	Event9
17	31-Jul	Event10
18	22-Apr	Event11
19
Perpetual Calendar

Cell Formulas
Range	Formula
EN8:EN12	EN8	=FILTER('Perpetual Calendar'!EK8:EK200,(MONTH('Perpetual Calendar'!EJ8:EJ200)=4)*(YEAR('Perpetual Calendar'!EJ8:EJ200)=2021))
EM8:EM18	EM8	=IFERROR(INDEX('Perpetual Calendar'!EK:EK,AGGREGATE(15,6,ROW('Perpetual Calendar'!EJ$8:EJ$200)/(4=MONTH('Perpetual Calendar'!EJ$8:EJ$200))/(2021=YEAR('Perpetual Calendar'!EJ$8:EJ$200)),ROWS(EM$8:EM8))),"")
Dynamic array formulas.

Eric, great formulas. Both produced the desired results.