3

I've been looking through the online help, but haven't been able to find the function or functions I need to accomplish the following.

I have 1 Google Spreadsheet with 2 Tabs. The 1st tab is a listing of players and the 2nd tab is a response sheet fed from a web form.

Sheet 1 - Players

       A               B          C
 1     Name            Score      Time
 2     smith, j
 3     jones, j
 4     davis, e

Sheet 2 - Results

       A               B          C
 1     Name            Score      Time
 2     jones, j        16         48.42
 3     smith, j        15         52.85
 4     smith, j        17         47.21

What I'd really like to do is to populate Sheet1 B2 with the MAX value for any result where Sheet2 An = Sheet 1 A2.

I know there is a "MAX" function in there, but I'm having trouble figuring it all out.

1

Somehow I knew if I asked the question, I'd stumble on an answer. The following formula worked for me.

 =arrayformula(max(if(Results!A2:A99=Players!A2,Results!B2:B99)))

I would be curious to know if there are any better methods out there.

1

I wouldn't call it better but this should be a 'one-formula' solution (unique names included), that outputs a table that auto-updates when new forms are submitted.

=ArrayFormula(vlookup(query({row(A2:A),sort(A2:C)},"select max(Col1) where Col2 <> '' group by Col2 label max(Col1)''",0),{row(A2:A),sort(A2:C)},{2,3,4},0))

Maybe give it a try and see if it works ?

1

Insert next formula only in Player!B1:

=Arrayformula(IF(ROW(B1:B)=1,"Score",IFERROR(VLOOKUP(A2:A,sort(Results!A2:C,2,0),2,0),)))

Insert next formula only in Player!C1

=Arrayformula(IF(ROW(C1:C)=1,"Time",IFERROR(VLOOKUP(A2:A,sort(Results!A2:C,2,0),3,0),)))

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.