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
Basketball   Bowling     Billiards   Badminton
----------   -------     ---------   ---------
John         Paul        Mark        Paul
Paul         Robert      James       Chris
Matt         Wesley      Brad        Dexter 

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:


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.
    – Dominic
    Jul 20 at 5:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.