Return MAX value from column B and Name from column A

W

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.
 
L

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
 
B

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
 
W

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top