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

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.

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.

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 ?

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