EN VI

Excel - 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?

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

• Formula used in cell C21

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

The above formula is dynamic array formula which uses firstly the LET() function, which helps in eliminating redundant calculations, avoids using repeated formulas/ranges and improves working functionalities of Excel by performance.
_Data variable is the source range, in the below example it starts from C3:C19 (It might be different in your data, needs to suit as per your data, also don't use merged cells, instead use center across selection if its needed for formatting or designing, still its better to avoid, and use single cell only).
_Names variable is the range on which the following function will be using as lookup_value and return the same one which matched as an output. J3:J7 (again change range as per your suit).
Using TEXTBEFORE() function extracting the names so that it can be used as a lookup_value to match with the original source and return the positions of the respective names as per the source data.
The above is used within XMATCH() to return the positions as mentioned above, and when not matched it will return as #N/A hence instead of using ISNUMBER() which can also be used, but used ISNA() which returns TRUE for errors while for numbers returns as FALSE reversing them by using 1-
Using FILTER() function now returning the output for those which are only TRUE values. and when there is nothing matched or if there is no data it will use the last param of the FILTER() and return as empty ""(in screenshot its not shown, formula shows that)

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,"-"))))