Return MAX value from column B and Name from column A

[wx4usa]

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.

 

[Lars-Åke Aspelin]

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

 

[barry houdini]

You say column C is a month....as text, e.g. "October"....or is it
date or something else?

What about duplicates. If max amount is duplicated for a specific mont
would you want all the matching names

 

[wx4usa]

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!