excelexcel-formulaformulalet

Trying to get names from a box on the right to automatically appear in another box when a different box has a value in it


hope the title wasn't too confusing.

this is my project:

teams

I'm trying to figure out how to get the names on the right (NICK-GL) to stack on top of each other at the bottom, when one of the mon-fri boxes has one of the correlating names in it, i.e., when Nick is is displayed in F3, NICK-GL appears in F21. The names on the right will change from week to week. I'd like them to automatically bump to the top of the list at the bottom but stay in the order they are on the Name list.

If you already couldn't tell, I'm a beginner.

I found this formula on stackoverflow, but I don't know how to implement it into my sheet.

=LET(datal,E74:E83,datar,T74:T82,dell,"/",delr,"-",
    dl,FILTER(datal,LEN(datal),""),
    IFNA(XLOOKUP(TEXTBEFORE(dl,dell),
        TEXTBEFORE(datar,delr),datar),dl))

If someone could also explain to me what each part of that means, I would tremendously appreciate it.


Solution

  • Well, if I have understood correctly, the following formula should work as per the given conditions:

    enter image description here


    • Formula used in cell C21

    =LET(_Data, C3:C19,_Names, $J3:$J7,FILTER(_Names,1-ISNA(XMATCH(TEXTBEFORE(_Names,"-"),_Data)),""))
    


    There is another shorter way of doing this is taking the advantage of COUNTIFS() or COUNTIF() function:

    =LET(
         _Data, C3:C19,
         _Names, $J3:$J7,
         FILTER(_Names,COUNTIF(_Data,TEXTBEFORE(_Names,"-"))))
    

    Using One Single Dynamic Array Formula to return the output:

    enter image description here


    =LET(
         _Data, C3:I19,
         _Name, J3:J7,
         _Rows, ROWS(_Name),
         _Colums, COLUMNS(_Data),
         IFERROR(MAKEARRAY(_Rows,_Colums,LAMBDA(r,c,
         INDEX(FILTER(_Name,1-ISNA(XMATCH(TEXTBEFORE(_Name,"-"),
         INDEX(_Data,,c))),""),r))),""))