Array Formula to return list of birthdays falling between a start and end date

[RicardoCubed]

Help appreciated with adjusting the below formula to include returning all birthday between a start and end date. the below works for the intended purpose of determining who has a birthday on the month and day indicated. I want to have the added functionality of adding for example all those who have a birthday a start date and end date.
Thanks for any help out there.

Excel 2010
	K	L	M	N
383			Month	Day
384	Names	Birthday	7	8
385	Joe	7/8/1965
386	Harry	5/7/1980
387			Will Populate below range with who has birthday
388			Joe
389			George
390	George	7/8/1965	Paul
391	Rick	6/8/1982	--
392	John	3/1/1968	--
393	Paul	7/8/1987	--
394	Ringo	6/8/1990	--
BDay

Cell Formulas
Range	Formula
M388	{=IFERROR(INDEX($K$385:$K$394,SMALL(IF(ISNUMBER($L$385:$L$394),IF(MONTH($L$385:$L$394)=$M$384,IF(DAY($L$385:$L$394)=$N$384,ROW($L$385:$L$394)-ROW($L$385)+1))),ROWS($L$385:L385))),"--")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

What are the start and end dates of interest?

 

[RicardoCubed]

Hi Aladin. to stick with the same data set say
start date = Month = 5 Day = 7
end date = Month = 6 Day = 8

so list would then have harry, rick and ringo.

Thanks!!!

 

[azumi]

But you dont have end date in your image, don't you?

 

[RicardoCubed]

the ultimate would be to be able to enter any of the month day year criteria in either start and/or end dates and get the filtered list, see below for illustration.

Excel 2010
	A	B	C	D	E	F
2			Criteria	Month	Day	Year
3	Names	Birthday	Start	9	1	1954
4	Joe	7/8/1965	End	6	8	1982
5	Harry	5/7/1980
6	Adam	8/1/1982	Will Populate below range with who has birthday
7	Lauren	9/1/1954	Lauren
8	Saul	10/22/1960	--
9	George	7/8/1965	--
10	Rick	6/8/1982	--
11	John	3/1/1968	--
12	Paul	7/8/1987	--
13	Ringo	6/8/1990	--
Sheet1

Cell Formulas
Range	Formula
C7	{=IFERROR(INDEX($A$4:$A$13,SMALL(IF(ISNUMBER($B$4:$B$13),IF(MONTH($B$4:$B$13)=$D$3,IF(DAY($B$4:$B$13)=$E$3,ROW($B$4:$B$13)-ROW($B$4)+1))),ROWS($B$4:B4))),"--")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

Hi Aladin. to stick with the same data set say
start date = Month = 5 Day = 7
end date = Month = 6 Day = 8

so list would then have harry, rick and ringo.

Thanks!!!

Row\Col	K​	L​	M​	N​
382​
383​			Month	Day
384​	Names	Birthday	5​	7​
385​	Joe	7/8/1965​	6​	8​
386​	Harry	5/7/1980​	Count	3​
387​			Anniversary Sublist
388​			Harry	5/7/1980​
389​			Rick	6/8/1982​
390​	George	7/8/1965​	Ringo	6/8/1990​
391​	Rick	6/8/1982​
392​	John	3/1/1968​
393​	Paul	7/8/1987​
394​	Ringo	6/8/1990​
395​

N386, control+shift+enter (cse), not just enter:

=SUM(IF(ISNUMBER(L385:L394),IF(MONTH(L385:L394)>=M384,
    IF(DAY(L385:L394)>=N384,IF(MONTH(L385:L394)<=M385,
    IF(DAY(L385:L394)<=N385,1))))))

M388, cse and copy down:

=IF(ROWS($M$388:M388)<=$N$386,INDEX($K$385:$K$394,
   SMALL(IF(ISNUMBER($L$385:$L$394),IF(MONTH($L$385:$L$394)>=$M$384,
   IF(DAY($L$385:$L$394)>=$N$384,IF(MONTH($L$385:$L$394)<=$M$385,
   IF(DAY($L$385:$L$394)<=$N$385,ROW($K$385:$K$394)-ROW($K$385)+1))))),
   ROWS($M$388:M388))),"")

N388, just enter and copy down:

=IF($M388="","",VLOOKUP(M388,$K$385:$L$394,2,0))

 

[RicardoCubed]

Aladin. Thank you. Just go back to this and works flawlessly!!!!
Appreciate it.

 

[Aladin Akyurek]

Aladin. Thank you. Just go back to this and works flawlessly!!!!
Appreciate it.

You are welcome.

 

[RicardoCubed]

Well - perhaps I messed it up because I was trying to query larger database and results wrong. I went back into the same, cut and pasted below. When I added Robin with date of 03-09-60 its not counting it or pulling in sublist. Yet when I added Tom it did. Suggestions???

Excel 2010
	K	L	M	N
383			Month	Day
384	Names	Birthday	3	1
385	Joe	7/8/1965	6	8
386	Harry	5/7/1980	Count	6
387	Aladin	5/8/1970	Anniversary Sublist
388	Robin	3/9/1960	Harry	5/7/1980
389	tom	3/4/1970	Aladin	5/8/1970
390	George	7/8/1965	tom	3/4/1970
391	Rick	6/8/1982	Rick	6/8/1982
392	John	3/1/1968	John	3/1/1968
393	Paul	7/8/1987	Ringo	6/8/1990
394	Ringo	6/8/1990
BDay

Cell Formulas
Range	Formula
N388	=IF($M388="","",VLOOKUP(M388,K385:L394,2,0))
N386	{=SUM(IF(ISNUMBER($L$385:$L$394),IF(MONTH($L$385:$L$394)>=M384,IF(DAY($L$385:$L$394)>=N384,IF(MONTH($L$385:$L$394)<=M385,IF(DAY($L$385:$L$394)<=N385,1))))))}
M388	{=IF(ROWS($M$388:M388)<=$N$386,INDEX($K$385:$K$394,SMALL(IF(ISNUMBER($L$385:$L$394),IF(MONTH($L$385:$L$394)>=$M$384,IF(DAY($L$385:$L$394)>=$N$384,IF(MONTH($L$385:$L$394)<=$M$385,IF(DAY($L$385:$L$394)<=$N$385,ROW($K$385:$K$394)-ROW($K$385)+1))))),ROWS($M$388:M388))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

That Robin case is a good counterexample. Thanks for the test.

Row\Col	K​	L​	M​	N​
382​			2015​
383​			Month	Day
384​	Names	Birthday	3​	1​
385​	Joe	7/8/1965​	6​	8​
386​	Harry	5/7/1980​	Count	5​
387​			Anniversary Sublist
388​			Harry	5/7/1980​
389​			Rick	6/8/1982​
390​	George	7/8/1965​	Robin	3/9/1960​
391​	Rick	6/8/1982​	John	3/1/1968​
392​	Robin	3/9/1960​	Ringo	6/8/1990​
393​	John	3/1/1968​
394​	Paul	7/8/1987​
395​	Ringo	6/8/1990​
396​

M382, just enter:

=YEAR(TODAY())

N386, control+shift+enter (cse), not just enter:

=SUM(IF(ISNUMBER($L$385:$L$395),
    IF(DATE($M$382,MONTH($L$385:$L$395),DAY($L$385:$L$395))>=DATE($M$382,$M$384,$N$384),
    IF(DATE($M$382,MONTH($L$385:$L$395),DAY($L$385:$L$395))<=DATE($M$382,$M$385,$N$385),
    1))))

M388, cse and copy down:

=IF(ROWS($M$388:M388)<=$N$386,INDEX($K$385:$K$395,
    SMALL(IF(ISNUMBER($L$385:$L$395),
    IF(DATE($M$382,MONTH($L$385:$L$395),DAY($L$385:$L$395))>=DATE($M$382,$M$384,$N$384),
    IF(DATE($M$382,MONTH($L$385:$L$395),DAY($L$385:$L$395))<=DATE($M$382,$M$385,$N$385),
    ROW($K$385:$K$395)-ROW($K$385)+1))),ROWS($M$388:M388))),"")

N388, just enter and copy down:

=IF($M388="","",VLOOKUP(M388,$K$385:$L$395,2,0))

See the link to the workbook that implements the last version:
https://dl.dropboxusercontent.com/u...ays falling between a start and end date.xlsx