Find all people attending on certain date

Question

I have a Google spreadsheet which keeps track of people attending some courses which run over sever weeks. Here are just 2 courses but my actual spreadsheet has 20 courses and different numbers of people attending each course.

In column L, I want a formula which calculates the next open date that the course is running (because they are closed on certain weeks when the venue is unavailable). In rows 1 and 7, some weeks are "closed" and some weeks are listed as "refunded" meaning that the course is closed this week and the attendees are refunded for this week. So L2 should be 10 May, and L8 should be 27 Apr.

And in cell L3 and below, it should show who will be attending on the next open date. I'd like this arrayformula in cell L3 to do this for all of the courses down below in column L. How can I do this?

	A	B	C	D	E	F	G	H	I	J	K	L
1	Wednesday course				Closed			Refunded		Closed		Next date
2	First name	Last name	ID	5 Apr	12 Apr	19 Apr	26 Apr	3 May	10 May	17 May	24 May	10 May
3	John	Smith	212	y		y	y		y		y
4	Henry	Jones	437			y		y		y	y
5	Katie	Rogers	894		y			y	y		y
6
7	Thursday course					Refunded			Closed			Next date
8	First name	Last name	ID	6 Apr	13 Apr	20 Apr	27 Apr	4 May	11 May	18 May	25 May	27 Apr
9	Jordan	Davies	014	y	y	y	y			y
10	Rose	McKenzie	709			y		y		y	y
11	Raj	Patel	338		y			y	y		y
12	Nathan	Green	338	y	y		y	y	y	y	y

Here's a shared Google spreadsheet: Google sheet link

Answer 1

Added a solution here. Please do test it out:

=let(Δ,scan(,byrow(U4:AP,lambda(Σ,if(isdate(index(Σ,,1)),+filter(column(Σ),Σ>=today(),not(regexmatch(offset(Σ,-2,),"Refund|Cancel"))),))),lambda(a,c,if(c<>"",c,a))),
       map(D4:D,Δ,lambda(Σ,Γ,if(Σ="",,index(offset(Σ,,-3,,42),,Γ)))))

---

final updated formula:

=let(Δ,scan(,byrow(U4:AP,lambda(Σ,if(isdate(index(Σ,,1)),+filter(column(Σ),Σ>=today(),not(regexmatch(offset(Σ,-2,),"Refund|Cancel"))),))),lambda(a,c,if(c<>"",c,a))),
       map(T4:T,Δ,lambda(Σ,Γ,if((Σ="Status")+(Σ="Week #"),,index(offset(Σ,,-19,,42),,Γ)))))