Formule RANG pour un classement

=DECALER('TABLEAU 1'!$B$3;;LIGNE()-7)

=LIGNE()-6

=DECALER('TABLEAU 1'!$B$4;;LIGNE()-7)

=RANG(B$4;'RECHERCHE PAR NOM'!$E$7:$E$46)

Function RangSurDeuxPlages(x, r1, Optional r2, Optional ordre = 0)
Dim n&, a#(), b#()
For Each r1 In r1
  If IsNumeric(CStr(r1)) Then
    n = n + 1
    ReDim Preserve a(1 To n)
    a(n) = r1
  End If
Next
If Not IsError(r2) Then
  For Each r2 In r2
    If IsNumeric(CStr(r2)) Then
      n = n + 1
      ReDim Preserve a(1 To n)
      a(n) = r2
    End If
Next
End If
ReDim b(1 To n)
If ordre Then
  For n = 1 To n
    b(n) = Application.Small(a, n)
  Next
Else
  For n = 1 To n
    b(n) = Application.Large(a, n)
  Next
End If
x = Application.Match(x, b, 0)
RangSurDeuxPlages = IIf(IsNumeric(x), x, "")
End Function

=RANG(B4;'TABLEAU 1:TABLEAU 2'!$B4:$U4)

Function SourceDeuxPlages(x, r1, Optional r2)
For Each r1 In r1
  If x = r1 Then SourceDeuxPlages _
    = "'" & r1.Parent.Name & "'!" & r1.Address(0, 0): Exit Function
Next
If Not IsError(r2) Then
  For Each r2 In r2
    If x = r2 Then SourceDeuxPlages _
      = "'" & r2.Parent.Name & "'!" & r2.Address(0, 0): Exit Function
  Next
End If
End Function

Sub Acces()
If [H14].Text <> "" Then Application.Goto Evaluate([H14].Text)
End Sub

Private Sub Label1_Click() 'OK
On Error Resume Next
Application.Goto ActiveSheet.[3:3,5:5].Find(ComboBox1, , xlValues, xlWhole)
If ActiveCell = ComboBox1 Then Exit Sub
Application.Goto Sheets("TABLEAU 1").[3:3,5:5].Find(ComboBox1)
If ActiveCell = ComboBox1 Then Exit Sub
Application.Goto Sheets("TABLEAU 2").[3:3,5:5].Find(ComboBox1)
End Sub

Private Sub UserForm_Initialize()
On Error Resume Next
With Sheets("LISTE") 'nom à adapter
ComboBox1.List = .[B7].Resize(Application.Match("zzz", .[B:B]) - 6).Value
End With
End Sub