Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return value from specific column if not in new column previously | Excel Worksheet Functions | |||
Search a column for values, return a value from adj column | Excel Worksheet Functions | |||
Return column number from column header text | Excel Discussion (Misc queries) | |||
Return text in Column A if Column B and Column K match | Excel Worksheet Functions | |||
Find max value in one column and return the value of corrosponding cell in different column | Excel Worksheet Functions |