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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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,

Kishan.

iGoogleDrive