# index match max!

This is a discussion on index match max! within the Excel Questions forums, part of the Question Forums category; =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 ...

1. ## 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

2. ## Re: 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

3. ## Re: index match max!

try this:

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

4. ## Re: 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))

5. ## Re: index match max!

Thank you gentlemen! I knew I was close.

6. ## Re: index match max!

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.

7. ## Re: 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)

8. ## Re: index match max!

=HLOOKUP(MAX(cvalues),first:last!C1:C2,-1,TRUE) Also returns a value error

Ideas?

9. ## Re: 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.

10. ## Re: 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.

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•