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
---------------
Jacob Skaria


"peteewheat11" wrote:

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!

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"peteewheat11" wrote:

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!

Super! Thanks for the very quick response!

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"peteewheat11" wrote:

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!

Here is a non-array solution:

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"peteewheat11" wrote:

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!