CODEWITHSUNDEEP
google-sheets
David
David

Reputation: 1873

Lookup field in table in google spreadsheet

I have the following table in a google spreadsheet, this will be the main sheet where the scores are entered:

   A           B       C       D      E
1)             Player  Game 1  Game 2 Game 3
2) 4/18/2013   Adam    152     180    180
3) 4/18/2013   Ben     100     180    170
4) 4/18/2013   Chris   121     167    160
5) 5/2/2013    Chris   143     153    140
6) 5/2/2013    Ben     150     111    140
7) 5/2/2013    Adam    115     100    130

I then want to be able to have an 'Adam' Sheet that just shows each players individual scores:

   A           B       C      D
1) Adam        Game 1  Game 2 Game 3
2) 4/18/2013   X       180    180
3) 5/2/2013    115     100    130

What is the formula I use to populate the X above from the table?

Upvotes: 2

Views: 1200

Answers (1)

AdamL
AdamL

Reputation: 24599

This is a formula that will populate X (and may be copied across and down):

=INDEX(FILTER('MainSheet'!C$2:C;'MainSheet'!$B$2:$B=$A$1;'MainSheet'!$A$2:$A=$A2);1)

This, entered in B2, will populate B2:D2 (and may be copied down):

=INDEX(FILTER('MainSheet'!C$2:E;'MainSheet'!B$2:B=A$1;'MainSheet'!A$2:A=A2);1)

This, entered in A1, will populate the entire table:

=QUERY('MainSheet'!A:E;"select A, C, D, E where B = 'Adam' label A 'Adam'";1)

Upvotes: 2

Related Questions