XL 2013 Résultat d'une liste déroulante suivant tableau

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

=SIERREUR(INDEX(Tableau!$A:$A;PETITE.VALEUR(SI((Tableau!$F$7:$P$21=$C7)*NON(NB.SI($C7:C7;Tableau!$A$7:$A$21));LIGNE(Tableau!$F$7:$P$21));1));"")

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