Lists of people

QuestionEdit

Im am managing a little league team. I have 12 players. Only a few are pitchers and catchers. I am trying to create a formula that when I designate someone a pitcher or catcher on that day, their name is listed under a separate column as such. I can get the first person on the list to come up using Vlookup, but if I have multiple players listed, the others dont show up in the subsequent rows. Ex:

Pitcher Adam Bobby Jacky

AnswerEdit

A few assumptions: Your master list is in A The indication that the person is a pitcher is in B with the"pitcher", like Player Adam pitcher Fred Bobby pitcher Joe Jacky pitcher Wallace

In D1 put "pitcher" in e2 (not E1) put =MATCH("pitcher",OFFSET($B$1,E1,0,1000,1),0)+E1 and fill down to E20 (or so). This column will be hidden. In D2: =IF(ISNA(E2),"",INDEX(A:A,E2)) and fill down to D20 or so -- this is your list of pitchers.

HTH