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

  • 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.