Home  |  About  | Last |  Submit  |  Contact
AllQuests.com



Previous Question:  Tricky Find in Text  Excel QuestionsNext Question:  Range  Excel Questions
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

 ABCDEFGH
1ABCDEF B
2587135  

Spreadsheet Formulas
CellFormula
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
ABCDEF
587185
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 1-4. 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

Previous Question:  Tricky Find in Text  MrExcel Message Board  Excel QuestionsNext Question:  Range  MrExcel Message Board  Excel Questions

- Source: index match max MrExcel Message Board Excel Questions
- Previous Question: Tricky Find in Text MrExcel Message Board Excel Questions
- Next Question: Range MrExcel Message Board Excel Questions