## Friday, August 16, 2013

### Find the value in Column A corresponding to the max number of a range

Question:

( by Surcam )

I have a spreadsheet set up to tally the scores of online college football games dynasty year.

Each row shows points scored for each team (Tulsa, UCLA, Wake Forest, Colorado, Kentucky)
Each column shows points scored against for each team.

 Year 1 TU UCLA WF CU UK TU NO GAME 31 50 17 21 UCLA 17 NO GAME 38 3 28 WF 0 7 NO GAME 28 28 CU 14 6 24 NO GAME 14 UK 17 3 21 17 NO GAME

I am attempting to list the teams with the highest and lowest point totals for a given year.  I can easily run =Max(B2:F6) to find the highest, but how do I grab the data in column A so I know which team did it?   My google fu has been unable to find the answer thus far even though I know it must be simple.

Thanks!

Screenshot of Sheet "Scores":

Solution:

Try any of the following formulas:

=filter('Scores'!A2:A6;('Scores'!B2:B6=max('Scores'!B2:F6))+('Scores'!C2:C6=max('Scores'!B2:F6))+('Scores'!D2:D6=max('Scores'!B2:F6))+('Scores'!E2:E6=max('Scores'!B2:F6))+('Scores'!F2:F6=max('Scores'!B2:F6)))

OR

=query('Scores'!A2:F6;"select A where B="&max('Scores'!B2:F6)&" or C="&max('Scores'!B2:F6)&" or D="&max('Scores'!B2:F6)&" or E="&max('Scores'!B2:F6)&" or F="&max('Scores'!B2:F6))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,