Welcome!
Unfortunately, the short syntax of the form 1_7 is not provided for indexes in a matrix. You can use syntax like 1;7 in the INDEX() function, like =INDEX(ListOfGroups;1;7). Or in the OFFSET() function. But in this case, it is advisable to have a cell above and to the left of your group list so that the function can refer to this cell as the base one. For example, if the list of groups is located in the range B2:K4 as in the attached file, then a formula of the form
="leader "&OFFSET($A$1;GroupNumber;LeaderNumber)&", members "&SUBSTITUTE(TEXTJOIN(" ";1;OFFSET($A$1;GroupNumber;1;1;10));OFFSET($A$1;GroupNumber;LeaderNumber)&" ";"")
will do the work
As you can see, the formulas are too long, but you just wanted to shorten the entry. It seems to me that using a custom function is what you are looking for. Not very complicated code
Function getGroup(aList As Variant, nGroup As Long, nLeader As Long) As String
Dim i As Long, sRes As String
For i = LBound(aList,2) To UBound(aList,2)
If i <> nLeader Then sRes = sRes & aList(nGroup, i) & ", "
Next i
getGroup ="leader " & aList(nGroup, nLeader) &", members " & Left(sRes, Len(sRes)-2)
End Function
and formulas
=GETGROUP($B$2:$K$4;1;7)
=GETGROUP($B$2:$K$4;2;4)
=GETGROUP($B$2:$K$4;3;8)
will give you the desired result immediately
getGroupLeader.ods (18.1 KB)
