index match max!

[justme]

=INDEX(A1:F2,MATCH(MAX(A2:F2),A2:F2,0),1)


The desriptions are in a row in cells A through F
The values are in the cells below

I want to find the max value and return the description above.

I come up with the formula above, which is not giving me the correct answer. I get the answer of 5 as shown below.

A B C D E F
5 8 7 1 3 5


5

 

[justme]

Jonmo1,

I just saw your code. I will also try this out. You can never have to many ways to conquer a problem. I'll let you know how it works.

Thank you.

 

[justme]

Aladin

I've started to put your code into my worksheet and find I can manipulate the flow and occurrencs, but I am having difficulty with pulling in the descriptions. I need to list the sheet name data in columns rather than rows. This is because I need to use this code in 14 rows of data. I would like the multiple occurences of the same max value to print in columns F2,G2, etc.....

I tried this formula, but get a #Name? error. The proper name of the worksheet list is SheetName50, so that is not a typo. Column E holds the occurence data. Column D holds the Max data.
Information on the SheetName50 is found in b1(descriptions) and b2(values).


=IF(COLUMNS($F$2:F2)<=$E$2,INDEX(T(INDIRECT("'"&SheetName50&"'!b1")),SMALL(IF(N(INDIRECT("'"&SheetName50&"'!b1"))=$D$2,ROW(SheetList)-ROW(INDEX(SheetName50,1,1))+1),colums($F$2:F2))),"")

I'm just sure you will be able to list the sheet names across columns in the same row. The final summary page layout is:

Maxvalues (col D) #of occurences (col E) Sheet Name (Col F) (col G) (col H)....etc.

Thank you again.

 

[Aladin Akyurek]

Aladin

I've started to put your code into my worksheet and find I can manipulate the flow and occurrencs, but I am having difficulty with pulling in the descriptions. I need to list the sheet name data in columns rather than rows. This is because I need to use this code in 14 rows of data. I would like the multiple occurences of the same max value to print in columns F2,G2, etc.....

I tried this formula, but get a #Name? error. The proper name of the worksheet list is SheetName50, so that is not a typo. Column E holds the occurence data. Column D holds the Max data.
Information on the SheetName50 is found in b1(descriptions) and b2(values).


=IF(COLUMNS($F$2:F2)<=$E$2,INDEX(T(INDIRECT("'"&SheetName50&"'!b1")),SMALL(IF(N(INDIRECT("'"&SheetName50&"'!b1"))=$D$2,ROW(SheetList)-ROW(INDEX(SheetName50,1,1))+1),colums($F$2:F2))),"")

I'm just sure you will be able to list the sheet names across columns in the same row. The final summary page layout is:

Maxvalues (col D) #of occurences (col E) Sheet Name (Col F) (col G) (col H)....etc.

Thank you again.

There is a typo: colums ==> COLUMNS...

=IF(COLUMNS($F$2:F2)<=$E$2,INDEX(T(INDIRECT("'"&SheetName50&"'!b1")),SMALL(IF(N(INDIRECT("'"&SheetName50&"'!b1"))=$D$2,ROW(SheetList)-ROW(INDEX(SheetName50,1,1))+1),COLUMNS($F$2:F2))),"")

 

[justme]

Working brilliantly. Thank you so much!

 

[Aladin Akyurek]

Working brilliantly. Thank you so much!

You are welcome. By the way, thank you too for bringing up such a problem, a kind that is rewarding to tackle with.