Filter Function with OR being similar

[jhammond10]

Hi all,
looking to create an array from a table using filter, one of which I couldn't do by applying regular filters so I hope it is possible.

The filter function is easy enough, and I can get it to work no problem with only one 'include' argument, but when I add the second it goes haywire.

The table (Table2) looks like this:

What I am trying to achieve is to get an array that when a user selects a team name (values in C or D), it will spill all of their team results.
I can do it for either 'Home' or 'Away' no problem using filter. For example if team "No Ma'am" was selected, I use =FILTER(Table2,Table2[Home]="No Ma'am")
and get

Same with 'Away' column for the include

The end result I'm looking for is to get both in the same array.

So whether Whether "No Ma'am" shows up in column C or D, return that row.

Thanks!

 

[SunnyAlv]

Like this ?

=FILTER(Table2,(Table2[Home]="No Ma'am")+(Table2[Away]="No Ma'am"))

 

[jhammond10]

Yes! Precisely like that. Missed some parentheses. Thank you so much, I was pulling my hair out.

 

[kevin9999]

Another option if you're amenable to a double-click VBA solution - put the following code in the sheet module of the sheet with the table. When you double-click on any team name in columns C or D it will only show that team (whether it's in column C or D) and double-clicking on cells C2 or D2 will show all teams.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)
    If Not Intersect(Target, Range("C2", Cells(Rows.Count, "D").End(xlUp))) Is Nothing Then
        Application.EnableEvents = False
        cancel = True
        Range("C3", Cells(Rows.Count, "C").End(xlUp)).EntireRow.Hidden = False
        If Target.Row > 2 Then
            Dim c As Range
            For Each c In Range("C3", Cells(Rows.Count, "C").End(xlUp))
                If c <> Target And c.Offset(, 1) <> Target Then c.EntireRow.Hidden = True
            Next c
        End If
    End If
        Application.EnableEvents = True
End Sub