Option Explicit
Function Résult(ByVal RngTit As Range, ByVal RngTab As Range, ByVal L As Long) ' As Range)
Dim TTit(), TLig(), C As Long, TRés(1 To 6, 1 To 1)
TTit = RngTit.Value
TLig = RngTab.Rows(L).Value
L = 0
For C = 1 To 6
If TLig(1, C) <> "" Then L = L + 1: TRés(L, 1) = TTit(1, C)
Next C
While L < 6: L = L + 1: TRés(L, 1) = "": Wend
Résult = TRés
End Function
=Résult($E$2:$J$2;$E$4:$J$16;$A$1)
=SIERREUR(INDEX(A$2:J$2;;PETITE.VALEUR(SI(INDEX(E$4:J$16;A$1;)="Ok";COLONNE(E$4:J$16));LIGNES(E$22:E22)));"")
=SIERREUR(INDEX(Tableau!$A:$A;PETITE.VALEUR(SI(Tableau!$F$7:$P$21=$C7;LIGNE(Tableau!$F$7:$P$21));COLONNES($D7:D7)));"")
Function Equipe$(x$, r As Range, ordre)
Set r = Intersect(r, r.Parent.UsedRange)
If r Is Nothing Then Exit Function
Dim t, ub%, i&, y$, j%, n, k&
t = r 'matrice, plus rapide
ub = UBound(t, 2)
For i = 1 To UBound(t)
If t(i, 1) <> "" Then y = t(i, 1)
For j = 5 To ub '5 => numéro de colonne modifiable
If t(i, j) = x Then
n = n + 1
If n = ordre Then Equipe = y: Exit Function
For k = i + 1 To ub
If t(k, 1) <> "" Then Exit For
Next k
i = k - 1
Exit For
End If
Next j, i
End Function