Private Sub ComboBox1_Change()Dim Surname As String
Dim arrData
Dim i As Long

    If Me.ComboBox1.Value <> "" Then
        Surname = Me.ComboBox1.Value
    Else
        Me.TextBox1.Text = ""
        Me.TextBox2.Text = ""
    End If
        
    arrData = Worksheets("Лист1").Range("A1").CurrentRegion.Value
    For i = 1 To UBound(arrData)
        If arrData(i, 1) = Surname Then
            Me.TextBox1.Text = arrData(i, 2) 'Имя
            Me.TextBox2.Text = arrData(i, 3) 'Отчество
            Exit For
        End If
    Next i
End Sub