Largest number in column

  • Thread starter Thread starter peteewheat11
  • Start date Start date
P

peteewheat11

I am working with football stats.

Column A is the player number. Column B is the yards for each play. A
typical sheet could look like:

<Player> <Yards>
10 21
10 14
10 7
14 1
14 3
10 22
14 11

I have a separate sheet that has each player number in a cell. I need to
find and display the longest yard made by each player.

10 (Longest Yards)
14 (Longest Yards)

Any help would be greatly appreciated!
 
In Sheet2 try the below formula..and copy down as required

Col A Col B
Player Max Yards
10 =MAX(IF((Sheet1!A1:A10=A2),Sheet1!B1:B10))
14 =

If this post helps click Yes
 
Forgot to mention that this is an array formula. Within the cell in edit mode
(F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

If this post helps click Yes
 
Super! Thanks for the very quick response!

Jacob Skaria said:
Forgot to mention that this is an array formula. Within the cell in edit mode
(F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

If this post helps click Yes
Click to expand...
 
Here is a non-array solution:

=SUMPRODUCT(MAX((Sheet1!A2:A12=A1)*Sheet1!B2:B12))
 
Back
Top