Return MAX value from column B and Name from column A
 

Is it possible for Excel to return the MAX value in column B and the
corresponding Name in column A For a month listed in column C

2000 rows Column A is Name, B is Amount and C is Month. I need to
return MAX of column B for each month scattered through the rows.

Return MAX value from column B and Name from column A
 

On Sat, 17 Oct 2009 10:15:32 -0700 (PDT), wx4usa
wrote:

Is it possible for Excel to return the MAX value in column B and the
corresponding Name in column A For a month listed in column C

2000 rows Column A is Name, B is Amount and C is Month. I need to
return MAX of column B for each month scattered through the rows.


Assuming that you have the months you want the Max values for in
column D, that the max value should appear in column E and the
corresponding name (from column A) should appear in column F and
that everything starts on row 1 in all columns, try the following:

In cell E1: =MAX(B$1:B$100*(C$1:C$100=D1))

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER

In cell F1: =INDEX(A$1:A$100,MATCH(E1,B$1:B$100,0))

Change the 100 in all places in these to formulas to fit the size of
your data in columns A and B.

Copy cells E1:F1 down as far as you have data in columns D.

Hope this helps / Lars-Åke

Return MAX value from column B and Name from column A
 

On Oct 17, 1:26*pm, Lars-Åke Aspelin
wrote:
On Sat, 17 Oct 2009 10:15:32 -0700 (PDT), wx4usa
wrote:

Is it possible for Excel to return the MAX value in column B and the
corresponding Name in column A For a month listed in column C

2000 rows Column A is Name, B is Amount and C is Month. *I need to
return MAX of column B for each month scattered through the rows.

Assuming that you have the months you want the Max values for in
column D, that the max value should appear in column E and the
corresponding name (from column A) should appear in column F and
that everything starts on row 1 in all columns, try the following:

In cell E1: =MAX(B$1:B$100*(C$1:C$100=D1))

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER

In cell F1: =INDEX(A$1:A$100,MATCH(E1,B$1:B$100,0))

Change the 100 in all places in these to formulas to fit the size of
your data in columns A and B.

Copy cells E1:F1 down as far as you have data in columns D.

Hope this helps / Lars-Åke

That worked brilliantly...thank you!