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

google sheets

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.

Best Answer

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.

Related Topic