Home  |  About  | Last |  Submit  |  Contact

 Tweet

 Question index match max ( MrExcel Message Board Excel Questions )Updated: 2010-07-23 16:10:17 (9)
 index match max =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
 Answers: index match max ( MrExcel Message Board Excel Questions )
index match max

Is it?

Sheet7

 A B C D E F G H 1 A B C D E F B 2 5 8 7 1 3 5

 Cell Formula H1 =INDEX(A1:F1,MATCH(MAX(A2:F2),A2:F2,0))

Excel tables to the web - Excel Jeanie Html 4

VoG

 index match max try this: =INDEX(A1:F1,MATCH(MAX(A2:F2),A2:F2,0)) btadams
 index match max You've got the row# and column# reversed in the Index.. You have =INDEX(Range,Column#,Row#) But it should be =INDEX(Range,Row#,Column#) Try =INDEX(A1:F2,1,MATCH(MAX(A2:F2),A2:F2,0)) or simpler, your range does not need to be the ENTIRE 2 row 6 column Range. You can just put the row 1. Then you don't have to specify the Row#, it's smart enough to figure it out. =INDEX(A1:F1,MATCH(MAX(A2:F2),A2:F2,0)) jonmo1
 index match max Thank you gentlemen! I knew I was close. justme
index match max

Quote:
 Originally Posted by 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
 A B C D E F 5 8 7 1 8 5 Max 8 Count 2 List B E

A1:F2 houses the data, headers and values...

A6:

=MAX(A2:F2)

A8:

=COUNTIF(A2:F2,A6)

A10:

Control+shift+enter, not just enter...
Code:
```=IF(ROWS(\$A\$10:A10)<=\$A\$8,INDEX(\$A\$1:\$F\$1,
SMALL(IF(\$A\$2:\$F\$2=\$A\$6,COLUMN(\$A\$1:\$F\$1)-COLUMN(\$A\$1)+1),
ROWS(\$A\$10:A10))),"")```
and copy down.