INDEX SMALL IF ROW for a list of names and birthdays

[Matthew Edminster]

I have a members list from which I would like to display a 3-column summary table of next month's birthdays sorted by day.

I have been trying to employ INDEX SMALL IF ROW in order to return this list, but something is missing. IF is returning the correct array of rows but it seems as if the "k" value of SMALL is wrong. Might this have something to do with the fact that I'm using dates?



Also, once that problem is fixed, will I be able to return the corresponding birthday and status values using the formulas I have indicated? Will sorting the resulting table by day mess things up?

 

[Hui]

F3: =IF(SUMPRODUCT(1*(MONTH($B$3:$B$13)=MONTH(TODAY())))< ROWS($E$3:E3),"",INDEX(A:A,SMALL(IF(MONTH($B$3:$B$13)=MONTH(TODAY()),ROW($A$3:$A$13)),ROW(A1)))) Ctrl+Shift+Enter

Copy across and down

or see the attached file:

To understand these formula have a read of:
http://chandoo.org/wp/2011/11/18/formula-forensics-003/

 

[Matthew Edminster]

Looks like that's the ticket. Not sure just why, but thanks for the link. I'll see what I can make of it. Thanks!

 

[Nebu]

Hi:

You can use the following non-array formula as well

Name:
=IFERROR(INDEX($A$3:$A$13,AGGREGATE(15,6,ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13)-1)/(MONTH($B$3:$B$13)=MONTH(TODAY())),ROW(A1))),"")
Birthday:
=IFERROR(INDEX($B$3:$B$13,AGGREGATE(15,6,ROW($B$3:$B$13)-MIN(ROW($B$3:$B$13)-1)/(MONTH($B$3:$B$13)=MONTH(TODAY())),ROW(B1))),"")

Status:

=IFERROR(INDEX($C$3:$C$13,AGGREGATE(15,6,ROW($B$3:$B$13)-MIN(ROW($B$3:$B$13)-1)/(MONTH($B$3:$B$13)=MONTH(TODAY())),ROW(C1))),"")

Formula on the colored cells

Thanks