Array formula to list data if MONTH and DAY fall between two dates

[SevenOut]

I have a workbook with two worksheets.
Sheet1 has a beginning date in D3 and an ending date in J3. (Schedule starting with Sat and ending with Fri).
Sheet2 has a list of birthdates. NAMES are A3:A47 and BIRTHDATES are B3:B47

So, my previous experience I've managed to work an array formula that would list the names and dates of birthdays for the current month. I'm having difficulty however adjusting the formula to find the names/dates if the month and day of Sheet2!B3:B47 fall between Sheet1!D3 and Sheet1!J3.

=IFERROR(INDEX($A$3:$A$47,SMALL(IF(MONTH($B$3:$B$47)=MONTH(Sheet1!$D$3),ROW($A$3:$A$47)-ROW($A$3)+1),ROWS($1:1))),"")

This formula for example, will only return data for the month of date in Sheet1!D3.

I do hope my explanation is clear, an example sheet can be worked up if needed.

 

[DanteAmor]

That is currently how the dates are set up... E3 =D3+1, F3= E3+1 and so on.

That formula was already on your sheet. The xl2bb tool put it there.
I just wanted to show the dates from 26dic2020 to 1ene2021 and you see how the formula on sheet2 already works for different years.

 

[SevenOut]

I appreciate your patience DanteAmor, I'm still trying to apply the formula. So far, incorrect or blank results.
Could you explain the first INDEX range of $A$1:$A$39? And the last reference of "-ROW($B$2)
My date formats are currently mm/dd/yy, would that affect what the formula reads?

 

[SevenOut]

INDEX more than the range of data to be specific. Sometimes I feel like I need Google Translator for excel speak.

 

[DanteAmor]

Sorry for that, my formula must be in all cells from E3 to E39.
Then copy the formula into cell E3
Select cells from E3 and up to E39.
Pres F2 key.
Now press Control+Shift+Enter.

Book1
	A	B	C	D	E	F
1
2	Name	Date			Name	Date
3	Name 1	01/01/1997			Name 7	05/02/1974
4	Name 2	02/01/1950			Name 8	05/02/1965
5	Name 3	14/01/1989
6	Name 4	21/01/1998
7	Name 5	27/01/1973
8	Name 6	28/01/1994
9	Name 7	05/02/1974
10	Name 8	05/02/1965
11	Name 9	27/03/1989
12	Name 10	29/03/1997
Sheet2

Cell Formulas
Range	Formula
E3:E12	E3	{=IFERROR(INDEX($A$1:$A$39, SMALL(IF(((DATE(YEAR(Sheet1!$D$3),MONTH($B$3:$B$39),DAY($B$3:$B$39))>=Sheet1!D3)*(DATE(YEAR(Sheet1!$D$3),MONTH($B$3:$B$39),DAY($B$3:$B$39))<=MAX((YEAR(Sheet1!$D$3:$J$3)=YEAR(Sheet1!$D$3))*(Sheet1!$D$3:$J$3))))+((DATE(YEAR(Sheet1!$J$3),MONTH($B$3:$B$39),DAY($B$3:$B$39))>=MIN((IF(YEAR(Sheet1!$D$3:$J$3)=YEAR(Sheet1!$J$3),Sheet1!$D$3:$J$3))))*(DATE(YEAR(Sheet1!$J$3),MONTH($B$3:$B$39),DAY($B$3:$B$39))<=Sheet1!$J$3)), ROW()),ROW()-ROW($B$2))),"")}
F3:F12	F3	=IFERROR(VLOOKUP(E3,$A$3:$B$39,2,0),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

_________________________________________________________________________________________________________
Check in my test file.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[SevenOut]

Okay okay, I was extending the array down maybe 10 rows, once I extended it to match the source range, it worked! I'd be lying if I said I understood it. Hope I can pay it forward helping somebody else down the road. I do want to thank everyone who've contributed to solving my question!!

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.

 

[Peter_SSs]

Here are some more options you might like to consider. They do not require the Ctrl+Shift+Enter confirmation and are a bit shorter as well.

I have made D3 and J3 in 'Schedule' named ranges (though we could just as easily use the cell references if you don't want to do this).
D3 is named D_Start
J3 is named D_End

SevenOut Workbook.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2
3				28-Dec	29-Dec	30-Dec	31-Dec	1-Jan	2-Jan	3-Jan
4	Lead Supervisor			SAT	SUN	MON	TUE	WED	THU	FRI
Schedule

Below, the formulas in columns E:F list the results oldest person to youngest. H:I list them in the order they appear in columns A:B, so choose whatever suits you best.
I have hidden a majority of the rows to keep the screen shot smaller.

SevenOut Workbook.xlsx
	A	B	C	D	E	F	G	H	I
1
2	Name	Date			Name	Date		Name	Date
3	Name 1	1/1/97			Name 2	2/1/50		Name 1	1/1/97
4	Name 2	2/1/50			Name 37	31/12/65		Name 2	2/1/50
5	Name 3	14/1/89			Name 36	28/12/82		Name 36	28/12/82
6	Name 4	21/1/98			Name 1	1/1/97		Name 37	31/12/65
7	Name 5	27/1/73
8	Name 6	28/1/94
36	Name 34	4/12/91
37	Name 35	13/12/85
38	Name 36	28/12/82
39	Name 37	31/12/65
Birthdays

Cell Formulas
Range	Formula
E3:E8,E36:E39	E3	=IF(F3="","",INDEX(A:A,AGGREGATE(15,6,ROW(A$3:A$39)/(B$3:B$39=F3),COUNTIF(F$3:F3,F3))))
F3:F8,F36:F39	F3	=IFERROR(AGGREGATE(15,6,B$3:B$39/(((DATE(YEAR(D_Start),MONTH(B$3:B$39),DAY(B$3:B$39))>=D_Start)+(DATE(YEAR(D_Start),MONTH(B$3:B$39),DAY(B$3:B$39))<=D_End)=2)+((DATE(YEAR(D_End),MONTH(B$3:B$39),DAY(B$3:B$39))>=D_Start)+(DATE(YEAR(D_End),MONTH(B$3:B$39),DAY(B$3:B$39))<=D_End)=2)>0),ROWS(F$3:F3)),"")
H3:H8,H36:H39	H3	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$3:A$39)/(((DATE(YEAR(D_Start),MONTH(B$3:B$39),DAY(B$3:B$39))>=D_Start)+(DATE(YEAR(D_Start),MONTH(B$3:B$39),DAY(B$3:B$39))<=D_End)=2)+((DATE(YEAR(D_End),MONTH(B$3:B$39),DAY(B$3:B$39))>=D_Start)+(DATE(YEAR(D_End),MONTH(B$3:B$39),DAY(B$3:B$39))<=D_End)=2)>0),ROWS(H$3:H3))),"")
I3:I8,I36:I39	I3	=IF(H3="","",VLOOKUP(H3,A$3:B$39,2,0))

Named Ranges
Name	Refers To	Cells
D_End	=Schedule!$J$3	H3:H8, H36:H39, F3:F8, F36:F39
D_Start	=Schedule!$D$3	H3:H8, H36:H39, F3:F8, F36:F39