Advanced Filter using "OR" for all 12 possibilities

I'm pretty good with Advanced Filters but I need them to be simpler in this case.

See the attached, where I've kept golf group foursomes log for many rounds of golf. I'd like to filter the left table for anyone named "Jim" and can do that with 4 rows of criteria. When it comes to Two different players it takes me 12 rows of criteria. I think this needs to be Combination of 4 things taken 2 at a time. The grey area is my criteria for the filter.
Advanced Filter Or Question.xlsm
The idea is to get all matches in the first/left table when either of the two players were in the same foursome.

I'd love a hint or two to simplify this filter and not need 12 rows to do it.

I don't think you can easily achieve this with Advanced Filter, but as you are now using Office 365 why not use FILTER function?

like:

Formula:

Please Login or Register  to view this content.

(in US version of excel change semicolons to comas as argument separators or just open the file and see how your excel reads the formula)

Moreover try to look for triples, not pairs with advanced filter :-D while with such formula you just add third segment:

Formula:

Please Login or Register  to view this content.

Of course a simple concept could de to use a helper column with check whether both names appeared in given row, and then use it for filter (with just one criterion) .
But you probably would like not to use the helper column,.
But this approach could be easily implemented with Power Query to keep a "neat design" of the worksheet.


A final comment: you have two Rons in data so both
Ron Zehnder Stan Blaske
and
Ron Arnold Stan Blaske
pairs are listed

May be it wopuld be wise to use first and family names in search

PS. I did also the PQ approach. The only non-standard part is the use of 2 named ranges with 2 names FirstPlayer and SecondPlayer to use them as parameters for the query:
So I manually added these two lines:

Please Login or Register  to view this content.

just after creating a "query based on range or table" when it contained only source and Changed Type lines. After addint=g the two above listed in line below Source, did all the rest in PQ editor.

The resulting code of the query:

Please Login or Register  to view this content.

again - see the attachment.

Please try

=LET(z,A2:D633,s,F2:G2,n,COLUMNS(s),b,INDEX(z,,1)&INDEX(z,,2)&INDEX(z,,3)&INDEX(z,,4),FILTER(z,MMULT(--ISNUMBER(SEARCH(s,b)),SEQUENCE(n,,,0))=n))

Hi Bo_Ry,

I'll need to follow your "LET" answer in a few days. I believe it is correct but don't know if my end-user has that function yet.

Hi, Marvin!

What about the other two contributors?

Hi wk9128,

I ended up using Kaper's answer but expanded it to 4 possible players and added a date and course columns also. I'll need to see why the "N(ISNUMBER(" is needed. Also the test names of "Ron" and "Stan" will change based on an input value but I think you knew that.

Hey Bo_Ry,

I've finally got a chance to use your spectacular LET formula above and find it much easier than all others. You are a formula star in my book.
Thanks again.

Hey Ali,

After a few extra days trying to get Kaper's formula to work on my larger dataset, I went back and tried Bo_Ry's formula. I'm almost ready to pay him for making the problem easier.
Thanks for the reminder to circle back around.