search data based on dropdown

Hellow Alaa,

The code below avoids looping through all entries in your data because that would take too long. Instead, it applies a filter, copies the filtered data to a temporary location to the right of the data in the sheet, removes the column you don't want, and then copies the remaining data to the result table. Any previous result is deleted. In the cleanup, the filter is removed and the temporary copy of filtered result deleted.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 276

    Const OutputRng As String = "F8"    ' change to suit

    Dim Rng         As Range            ' range to filter / display result
    Dim TmpRng      As Range            ' blank range for temporary use

    If Not Intersect(Target, [G5]) Is Nothing Then
        With Application
            .EnableEvents = False       ' prevent change to call this procedure
            .ScreenUpdating = False     ' freeze the monitor
        End With

        With ActiveSheet
            Set Rng = .Range(OutputRng)
            .Range(Rng, .Cells(.Rows.Count, Rng.Column).End(xlUp)).Resize(, 3).ClearContents
            If .AutoFilterMode Then .Cells.AutoFilter
            With .UsedRange
                Set TmpRng = Cells(1, .Column + .Columns.Count + 1)
            End With

            Set Rng = .Range("A3").CurrentRegion
            ' Field 2 = column B
            Rng.AutoFilter Field:=2, Criteria1:=.Cells(5, "G").Value
            Rng.Offset(1).SpecialCells(xlCellTypeVisible).Copy Destination:=TmpRng
            .Columns(TmpRng.Column + 1).Delete

            .Cells.AutoFilter
            With TmpRng.CurrentRegion
                .Copy Destination:=.Worksheet.Range(OutputRng)
                .Columns.EntireColumn.Delete
            End With
        End With

        With Application
            .EnableEvents = True            ' re-enable worksheet events
            .ScreenUpdating = True          ' update the display
        End With
    End If
End Sub

The code is all in the event procedure. A working copy is included in the attached workbook. Your previous code in Module1 is not used by this solution.