Find, copy and paste specific values.

See if this is fast enough.
For the layout for output range in uploaded workbook.

Sub test()
    Dim a, x, i As Long, ii As Long, n As Long, myCol, Emp As String, flg As Boolean
    myCol = Array(2, 6, 7, 8): Emp = Sheets("sheet1").ComboBox1.Value
    x = Filter([transpose(if(r1:r10000<>"",r1:r10000))], False, 0)
    a = Cells(1).CurrentRegion.Value
    For i = 2 To UBound(a, 1)
        If (a(i, 1) = Emp) * (a(i, 7) > 0) Then
            For ii = 0 To UBound(x)
                If a(i, 2) = x(ii) Then flg = True: Exit For
            Next
            If Not flg Then
                n = n + 1
                For ii = 0 To UBound(myCol)
                    a(n, ii + 1) = a(i, myCol(ii))
                Next
            End If
            flg = False
        End If
    Next
    Range("k23").CurrentRegion.Offset(2).ClearContents
    If n > 0 Then
        With Range("k24").Resize(n, UBound(myCol) + 1)
            .Value = a
            .Sort .Cells(1, 2), 2, , .Cells(1, 3), 2
        End With
    Else
        MsgBox "No match"
    End If
End Sub

Thank you for reply, I will have to check it at work for final verdict, but so far it looks amazing and much faster than formulas. I have one more question. As I understant, adding "Call test" at Sub ComboBox1_Change() is the way to go? Is there any way to stop this macro from running whenever I write text to find instead of selecting it from drop down list? With formulas I just used:
ComboBox1_GotFocus() Application.Calculation = xlManual
ComboBox1_LostFocus() Application.Calculation = xlAutomatic
which won't work in this situation.

Follow the step.
1) ThisWorkbook code Module

Private Sub Workbook_Open()
    If ActiveSheet Is Sheets("Sheet1") Then Run Sheets("sheet1").CodeName & ".worksheet_activate"
End Sub

2) Sheet1 sheet code module

Option Explicit

Private Sub ComboBox1_Change()
    Range("k23").CurrentRegion.Offset(2).ClearContents
    If Me.ComboBox1.ListIndex = -1 Then Exit Sub
    Application.ScreenUpdating = False
    test
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Activate()
    Dim a, e
    a = Cells(1).CurrentRegion.Columns(1).Offset(1).Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For Each e In a
            If e <> "" Then .Item(e) = Empty
        Next
        Me.ComboBox1.List = .keys
    End With
End Sub

See attached

Just change the event to Click

Private Sub ComboBox1_Change()

to

Private Sub ComboBox1_Click