excelfilterexcel-formulaexcel-2021

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

Lookup table

Bookings 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:

    enter image description here

    =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!