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

Question

hope the title wasn't too confusing.

this is my project:

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.

Answer 1

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

---

Using One Single Dynamic Array Formula to return the output:

---

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

---

Answer 2

So the LET function lets you name and 'stack' calculations/variables; I'll break it down for you:

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

datal,E74:E83 selects the range E74 to E83, and calls it 'datal';

datar,T74:T82 does the same for range T74 to T82, and calls it 'datar';

dell is a variable later used to put in "/";

delr does the same but with "-";

dl,FILTER(datal,LEN(datal),"") filters the 'datal', based on the lenght of text string in the selected range, or outputs nothing ("") if nothing is found; -I'm not exactly sure how the LEN function is used to filter data-

finally we have the calculation part of the formula: IFNA(XLOOKUP(TEXTBEFORE(dl,dell),TEXTBEFORE(datar,delr),datar),dl))

IFNA wraps the formula, it's a variaton of IFERROR; when the included formula returns NA error, it then will output 'dl'.

XLOOKUP will search for the 'dl' variable (which we named above), but with only a part of that data: the TEXTBEFORE is used on text, but before a delimeter (dell, or "/"); in 'datar', but again only partially, this time with the text before the "-" (delr).

Then XLOOKUP returns from 'datar'.

I'm assuming you know a bit about XLOOKUP here.

Hope you learn something from it, if something is still unclear, don't hesitate to ask for help. Hopefully I or someone else can bring some more clarification.