# Google Sheet Filter and Match (List the label where a name exist.)

I have 4 game columns that contain the names of the players. I want to list the games of a particular player.

``````     A.         B.          C.          D
----------   -------     ---------   ---------
John         Paul        Mark        Paul
Paul         Robert      James       Chris
``````

Now I want to create a formula and list all the games Paul played. Appreciate the help in advance.

Ok, now this looks good!!! (yet complicated)

1. create 2 named ranges: 1: lst_hds (range(A1:D1) to contain the game names, 2: lst_db (range(A3:D5)) to contain the players.

2. still in B10 the name you search for...

3. now copy-paste: `=arrayformula( if(mid( text(sum( ( if( lst_db=\$B\$10, 10^(column(lst_db)-1),0) ) ),"0000") ,columns(lst_hds)+1-COLUMN(lst_hds),1)="1",lst_hds,"") )`

now this is much easier to expand by adjusting the ranges!!! Off-course you can transpose it to get vertical list.

I keep the 1st formula for reference:

``````=FILTER(A1:D1,{countif(A3:A5,B10),countif(B3:B5,B10),countif(C3:C5,B10),COUNTIF(D3:D5,B10)})
``````

hope it helps

EDIT Oct 2021 Just added one even simpler solution (actually two but very similar). Let's see them:

1. `=arrayformula( if( mmult( {1,1,1}, arrayformula( if(A3:D5=\$B\$10,1,0) ) )=1,A1:D1,""))`
... or =arrayformula( if( mmult( {1,1,1}, arrayformula( if(lst_db=\$B\$10,1,0) ) )=1,lst_hds,"")) with our lists! Expand with lists as they grow and Adjust only the {1,1,1} array according to ROWs

2. using Filter and thus getting only the results (without empty cells in between) `=filter(lst_hds, mmult({1,1,1}, ( if(lst_db=\$B\$10,1,0) ) )=1)` again adjust {1,1,1} according to ROWs

• Hi Apostolos55! Thank you for your reply. For anyone who wants to display the results vertically, add TRANSPOSE at the beginning and open-close the whole formula of Apostolos55. Thanks for the help, again! I appreciate it. Jul 20 at 5:05