Need Help VBA or Formula

[J@rv!s]

I need formula or VBA code to pull some records in this scenario there are some teams they are going play matches with each others.
All I need is if I put Team name in Cell I1 below that cell all the records should show or extract from the table there are two columns for the teams the team which I am looking for should be any where in two columns.

I have uploaded an excel file

Thanks in advance.

 

[vletm]

@J@rv!s
One possible would be something like this ...
Ps. follow instructions

 

[bosco_yip]

Formula way,

1] F2, enter formula :

=IF((B2=I$1)+(D2=I$1),MAX(F$1:F1)+1,"")

and, F2 >> Custom Cell Formatting >> in the type box enter: ;;;
all copy down

2] J1, enter formula :

=LOOKUP(9^9,F:F)

and, J1 >> Custom Cell Formatting >> in the type box enter: ;;;

3] I4, formula copy across and down :

=IF(ROWS(A$1:A1)<=$J$1,INDEX(A$2:A$22,MATCH(ROWS(A$1:A1),$F$2:$F$22,0)),"")

Regards
Bosco

 

[jindon]

I need formula or VBA code to pull some records in this scenario there are some teams they are going play matches with each others.

VBA, if you like
To Sheet1 code module
This will be fired whenever you change the value in I1.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x
    If Target.Address(0, 0) <> "I1" Then Exit Sub
    With Intersect(Columns("a:e"), UsedRange)
        x = Filter(Evaluate("transpose(if((" & .Columns(2).Address & "=i1)+(" & .Columns(4).Address & _
        "=i1)+(row(" & .Address & ")=1),row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0)
        If UBound(x) > 0 Then
            x = Application.Index(.Value, Application.Transpose(x), [{1,2,3,4,5}])
        Else
            x = Empty
        End If
    End With
    With Application
        .EnableEvents = False: .ScreenUpdating = False
    End With
    With [i3].CurrentRegion
        With .Offset(1)
            .Borders.LineStyle = xlNone: .ClearContents
        End With
        If IsArray(x) * (Target.Value <> "") Then
            With .Resize(UBound(x, 1))
                .Value = x
                .FormatConditions.Delete
                With .FormatConditions.Add(Type:=2, Formula1:="=i3=$i$1")
                    .Font.Bold = True
                    .Font.Color = 192
                End With
                .Borders.Weight = 2
            End With
        End If
    End With
    With Application
        .EnableEvents = True: .ScreenUpdating = True
    End With
End Sub

 

[J@rv!s]

@J@rv!s
One possible would be something like this ...
Ps. follow instructions

This is cool working for me thanks a lot jindon,vletm and bosco_yip. I learn three tricks today...!