I need to fix this formula so that the columns letters go UP as I drag DOWN

So specifically with the following formula, how do i fix make the range say =B2:b21, =C2:c21. =D2:d21, etc when i drag down?

The full formula is:

=TEXTJOIN(", ",TRUE,FILTER('Staff Rota'!$A$2:$A$21,('Staff Rota'!B2:B21="W")*('Staff Rota'!B2=D2)))

Lookup table

Bookings table

Really appreciate some guidance on this.

I have tried so match with index match etc but to no avail

>Solution :

Try using the following formula:

=TEXTJOIN(", ",1,FILTER($A$4:$A$10,FILTER($B$4:$E$10,G2=$B$2:$E$2,"")="W",""))

Or, Can use XLOOKUP() + FILTER()

=TEXTJOIN(", ",1,FILTER($A$4:$A$10,XLOOKUP(G2,$B$2:$E$2,$B$4:$E$10,"")="W",""))

Or, can spill the whole range using a LAMBDA() helper function called MAP()

=MAP(G2:G11,LAMBDA(α, 
 TEXTJOIN(", ",1,FILTER(A4:A10,
 FILTER(B4:E10,B2:E2=α,"")="W",""))))

And also, since you have mentioned that tried using INDEX()+MATCH() but no avail then here is using INDEX()+MATCH() <– This will be same with CHOOSECOL()+XMATCH()

=TEXTJOIN(", ",1,FILTER(A$4:A$10,IFNA(INDEX(B$4:E$10,,MATCH(G2,B$2:E$2,0)),"")="W",""))

You will need to change the range and references as per your suit!