GoogleSheets: Showing next upcoming date(s) in date range

I have the following formula working well at the moment which shows the next upcoming birthday from a cell range (Column N);

=ArrayFormula(TO_DATE(MIN(if(N1:N>today(),N1:N))))

My only problem is that it will only deliver one result and it is possible that multiple results are true, the date range in column N is a list of birthdays and some will be on the same day.
I need a way of knowing if there is more than one result and what that result may be.
If anyone knows of a way to help with this problem I would really appreciate it.
Thanks in advance x

Your formula involves a MIN call. Any & every spreadsheet I've ever used returns one and only one value from MIN. Likewise from MAX, COUNT[A], SUM, and various descriptive statistics functions.

If col N contains only dates, and you want all dates after today(), why not just use =filter(N1:N,N1:N>today()) ?

I have also tried using the following formula;

=IF(ROW()<=5,SMALL($N$1:$N$198,ROW()),"")

this shows me the next 5 dates in the column after today and it includes any duplicates which is great, but there is a separate column of names of people to whom the birthdays belong to, I want my "Next 5 Dates" to show the names of the people too.
When I use the following formula I get the names of the people but the people with the same birthdays are coming up as incorrect.

=IF(E2<>"",INDEX($A$1:$A$198,MATCH(E1,$N$1:$N$198,0)+COUNTIF($E$1:E1,E1)-1),"")

What I get instead is the name in the next cell down when a duplicate is being referred to. (Names are in column A, Dates are in column N, and the desired result should show in column E)
I'm sure there is a simple solution, can you point me towards it?

Originally Posted by HayleyE

. . .

=IF(ROW()<=5,SMALL($N$1:$N$198,ROW()),"")

this shows me the next 5 dates . . . there is a separate column of names of people to whom the birthdays belong to, . . .

=IF(E2<>"",INDEX($A$1:$A$198,MATCH(E1,$N$1:$N$198,0)
+COUNTIF($E$1:E1,E1)-1),"")

What I get instead is the name in the next cell down when a duplicate is being referred to. . . .

Unless your list of birthdays in N1:N198 is sorted in ascending order, and the names in A1:A198 sorted along with it, the logic in your 2nd formula is wrong. The row below the topmost match for E1 in N1:N198 may not have the same or next birthday. If your A1:A198 and N1:N198 ranges were sorted by col N in ascending order, you'd only need to find the first date after today, then directly index that row and the next 4 or more.

Anyway, in Google Sheets, use the FILTER function. If you want everyone with the 5th soonest and sooner birthdays,

=sort(filter({N1:N198,A1:A198},N1:N198<=small(N1:N198,5)),1,1)

If you want an Excel-like approach, the formulas are much longer and/or need additional cells per result. Take advantage of what Google Sheets provides.

Originally Posted by hrlngrv

Unless your list of birthdays in N1:N198 is sorted in ascending order, and the names in A1:A198 sorted along with it, the logic in your 2nd formula is wrong. The row below the topmost match for E1 in N1:N198 may not have the same or next birthday. If your A1:A198 and N1:N198 ranges were sorted by col N in ascending order, you'd only need to find the first date after today, then directly index that row and the next 4 or more.

Anyway, in Google Sheets, use the FILTER function. If you want everyone with the 5th soonest and sooner birthdays,

=sort(filter({N1:N198,A1:A198},N1:N198<=small(N1:N198,5)),1,1)

If you want an Excel-like approach, the formulas are much longer and/or need additional cells per result. Take advantage of what Google Sheets provides.

This works perfectly, thank you!!