Combined Filter Results

[uk747]

HI

Trying to combine the results of 4 filters from the same Data
What I need to do is look for where ever Dave appears in Name1, Name2, Name3 or Name 4 Column and show those Rows.
Required result is at the bottom which shows only Rows where Dave appears and doesn't include duplicate rows where he might appear in more than 1 column

Individual filters would be =FILTER($C$3:$H$14, C3:C14= "Dave") =FILTER($C$3:$H$14, D3:D14= "Dave") =FILTER($C$3:$H$14, E3:E14= "Dave") =FILTER($C$3:$H$14, F3:F14= "Dave")
But not sure how to get above filters together(removing duplicates)

Data Below is in C2:H14(Headers are in Row 2)

Name1	Name2	Name3	Name4	Value1	Value2
Fred	Dave	Dave	Ben	6332​	5122​
Harry	Fred	Fred	Ben	7979​	5898​
George	Dave	Dave	Fred	7847​	8882​
Mary	Clive	Dave	Ben	7825​	7426​
Fred	Dave	Dave	Ben	9567​	9215​
Harry	Fred	Fred	Dave	6783​	9997​
George	Dave	Dave	Fred	5034​	7957​
Mary	Clive	Tom	Ben	5504​	5087​
Mary	Dave	Dave	Ben	5887​	8104​
Harry	Fred	Fred	Ben	7581​	7460​
George	George	Harry	Fred	8673​	6727​
Mary	Clive	Dave	Ben	8958​	6284​

Required Result

Name1	Name2	Name3	Name4	Value1	Value2
Fred	Dave	Dave	Ben	6332​	5122​
George	Dave	Dave	Fred	7847​	8882​
Mary	Clive	Dave	Ben	7825​	7426​
Fred	Dave	Dave	Ben	9567​	9215​
Harry	Fred	Fred	Dave	6783​	9997​
George	Dave	Dave	Fred	5034​	7957​
Mary	Dave	Dave	Ben	5887​	8104​
Mary	Clive	Dave	Ben	8958​	6284​

 

[Fluff]

How about

=FILTER(C3:H14,BYROW(C3:F14,LAMBDA(br,SUM(--(br="Dave")))))

 

[uk747]

How about

=FILTER(C3:H14,BYROW(C3:F14,LAMBDA(br,SUM(--(br="Dave")))))

Thanks for the above. Is there a version without Lambda
using 2 pcs. one works but my main pc which also has 365 but unfortunately formula above doesn't work

 

[Fluff]

How about

=FILTER(C3:H14,MMULT(--(C3:F14="Dave"),SEQUENCE(COLUMNS(C3:F3),,,0)))

 

[Georgiboy]

Another option - combining ranges:

=FILTER(C3:H14,ISNUMBER(SEARCH("Dave",C3:C14&D3:D14&E3:E14&F3:F14)))

 

[uk747]

Thanks Guys!

Fluffs original solution and MMULT worked on a version that had the newer functions and Georgiboy for all versions including legacy ones

 

[Fluff]

Glad we could help & thanks for the feedback.
My 2nd formula & Georgieboy's formula will only work with either 365 or 2021, nothing earlier.