Selecting the Max (or MIN) value in a field from only those rows that match criteria in another sheet

Question

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.

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

Answer 2

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 ?

Answer 3

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),)))