Question index match max ( MrExcel Message Board Excel Questions ) Updated: 20100723 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
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.
Aladin Akyurek


index match max
If my information is found in multiple worksheets and I name some ranges. My test example has descriptions in cell C1 and Values in C2. I've named the ranges in Sheets 14. Descriptions= first:last!$C1, CValues = first:last!$C2
I am able to find: Max(CValues).
Now I want to find the description for the Max(CValues)
I've tried: =INDEX(Descriptions,MATCH(MAX(cvalues),cvalues,0)). This returns a #value!error.
I've also referenced the cell where I tested to see if it would find the max value over multiple worksheets. In cell A4 I have entered: max(cvalues) edit: (This returns the #Value! error)
Then my equation became: =index(descriptions,match(a4),cvalues,0))
This returns the error that I've entered too few arguemens for this function.
Likewise with the lookup function: =LOOKUP(MAX(cvalues),cvalues,Descriptions)
justme


index match max
=HLOOKUP(MAX(cvalues),first:last!C1:C2,1,TRUE) Also returns a value error
Ideas?
justme


index match max
Using the example in your first post,
=INDEX(A1:F1,MATCH(MAX(A2:F2),A2:F2,0))
if you name A1:F1 "Descriptions" and A2:F2 "CValues" then the formula
=INDEX(Descriptions,MATCH(MAX(cvalues),cvalues,0))
should work.
btadams


index match max
Good morning. That is exactly what I thought. However, in post #7 that is exactly what I tried to do and it returns a #VALUE! error.
justme


 Source:  Previous Question: MrExcel Message Board Excel Questions  Next Question: MrExcel Message Board Excel Questions 

