Formula to create contract expiry lists based on date

[The_Snook]

Morning all,

I have a schedule of contracts which I'd like to summarise into different lists based on when they expire, e.g. <1 month, <6 months.

I've attached an example of the data I'm working with.

If you have any queries just give me a shout.

Thanks in advance,

Snook

[samba_ravi]

for <1
=IFERROR(INDEX($A:$A,SMALL(INDEX((DATEDIF(TODAY()-1,$B$2:$B$27,"m")>0)*10^10+ROW($B$2:$B$27),0),ROWS(D$2:D2))),"")
for <6
=IFERROR(INDEX($A:$A,SMALL(INDEX((DATEDIF(TODAY()-1,$B$2:$B$27,"m")>5)*10^10+ROW($B$2:$B$27),0),ROWS(F$2:F2))),"")
for < 12
=IFERROR(INDEX($A:$A,SMALL(INDEX((DATEDIF(TODAY()-1,$B$2:$B$27,"m")>11)*10^10+ROW($B$2:$B$27),0),ROWS(H$2:H2))),"")
try the above formula and copy towards down

[The_Snook]

Absolutely bob on, cheers samba_ravi!

[The_Snook]

I've just noticed that the formula falls over when the data contains expired agreements. How can I amend it to take account of these?

Thanks in advance,

Snook

[Glenn Kennedy]

maybe this (and similar for 6 & 12 months):

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($B$2:$B$27)/(($B$2:$B$27 > TODAY())*($B$2:$B$27 < EDATE(TODAY(),1))),ROWS($1:1))),"")

[The_Snook]

Just the job, cheers Glenn Kennedy!

[Glenn Kennedy]

you're welcome and thanks for the rep.