Sunday, March 10, 2013

yogi_Compile Team Names From Table in Sheet Matrix Where Players In Range A6 To A9 Of yogi_Lookup Participate

                                          Google Spreadsheet   Post  #1077
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Mar 10, 2013
user meph2u :(!mydiscussions/docs/EjtbzLilb3I)

I would like to query either sheet 2 or sheet 3 (they have the same data) where starting in A2 and going down I input up to 10 player names and in column B results displayed are a list of the teams that have those players on them.

Is this possible?

For the data in Lookup: A2 - A5

McIlroy, Rory
Rose, Justin
Kuchar, Matt
Simpson, Webb

I would expect to get the following results:
Crozier, Kevan
Matheson, Rick
Rogers/ Campbell

As these are the only three teams that have all four of the players on them.   (I calculated this result manually, and may have made some mistakes)

Every actual Team/Player combination is in 2013 Teams
 has a matrix where the teams are across row 2 and the players down Column D, and an "X" appears if the player is actually on the team.

So one way to ask the question is:

What teams in MATRIX:Row 2 for ALL of  the players in LOOKUP:A2...A have an "X" in the cell in MATRIX which is the intersection of Column D (Player) and Row 2 (Team)?

Another way to ask the question,

Is for the players in LOOKUP:A2...A, when considering 2013 TEAMS:Column F, Which teams in Column B have a rows in 2013 TEAMS that have the Team (B) combined with the Player (F)?

The goal if for the pool members to be able to put in the top 3-10 players on the leaderboard and see which teams in the pool have ALL of these players.

Please ask more questions if I have made it clear yet....and thank you for your patience.   I greatly appreciate your help.

following is a solution to the problem