=ИНДЕКС(B:C;ПОИСКПОЗ(E4;B:B;0);2):ИНДЕКС(B:C;ПОИСКПОЗ(E4;B:B;0)+СЧЁТЕСЛИ(B:B;E4)-1;2)

    With Sheets("пром.итог классов")
        On Error Resume Next
        a_ = Range("E4")
        r_ = WorksheetFunction.Match(a_, .Range("B:B"), 0)
        n_ = WorksheetFunction.CountIf(.Range("B:B"), a_) - 1
        ActiveSheet.ComboBox1.ListFillRange = "'" & .Name & "'!" & .Range("B" & r_).Resize(n_).Address
    End With

Option Explicit

Private Sub ComboBox2_Change()
Dim rowLast As Integer, i%

Me.ComboBox1.Clear

With Worksheets("пром.итог классов")
rowLast = .UsedRange.Rows.Count

For i = 2 To rowLast
If .Cells(i, 1).Value = "" Then Exit For

If .Cells(i, 3).Value = Me.ComboBox2.Value Then
Me.ComboBox1.AddItem .Cells(i, 4).Value

End If
Next i
End With

End Sub

Private Sub UserForm_Initialize()
Dim oDict
Dim rowLast As Integer, i%

Set oDict = CreateObject("Scripting.Dictionary")
oDict.CompareMode = 1
Me.ComboBox2.Clear
With Worksheets("пром.итог классов")
rowLast = .UsedRange.Rows.Count

For i = 2 To rowLast
If .Cells(i, 1).Value = "" Then Exit For

    If Not oDict.exists(.Cells(i, 3).Value) Then
    oDict.Add .Cells(i, 3).Value, 1
    Me.ComboBox2.AddItem .Cells(i, 3).Value
    End If
Next i

End With
End Sub

Private Sub ComboBox11_Change()
   Dim myFormula As Integer
   myFormula = WorksheetFunction.Index(Worksheets("шифры").Range("C2"), Match(ComboBox8, Worksheets("шифры").Range("C2"), 0) * 2): INDEX _<br / <img rel="usm" src="/sml2/angry.gif" border="0" align="absmiddle" alt=">(" /> Worksheets("шифры").Range("C2"),MATCH(ComboBox8,Worksheets("шифры").Range("C2"),0)+COUNTIF(Worksheets("шифры").Range("C2"),ComboBox8)-1,2)
End Sub

MATCH(ComboBox8,Worksheets("шифры").Range("C2"),0)