Problem with INDEX formula
 

Hello,

I have a formula =INDEX(C4:C33,MATCH(MAX(E4:E33),E4:E33,0 which looks for
the maximum value in the range E4:E33 and displays the name of the person
which is equivalent in the range C4:C33.

C4:C33 has names and their respective values are there in range E4:E33.

The problem is if the max value is the same for two or more persons.

Please let me know how can the formula be changed that it shows all the
names if the max value is same for 2 or more persons.

Thanks in advance
Sasikiran

Problem with INDEX formula
 

Hi Sasikiran,
One method of achieving the goal might be to use a Filtered List
Excel Filters -- AutoFilter Tips
http://www.contextures.com/xlautofilter02.html

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Sasikiran" wrote in message ...
Hello,

I have a formula =INDEX(C4:C33,MATCH(MAX(E4:E33),E4:E33,0 which looks for
the maximum value in the range E4:E33 and displays the name of the person
which is equivalent in the range C4:C33.

C4:C33 has names and their respective values are there in range E4:E33.

The problem is if the max value is the same for two or more persons.

Please let me know how can the formula be changed that it shows all the
names if the max value is same for 2 or more persons.

Thanks in advance
Sasikiran

Problem with INDEX formula
 

Hi Sasikiran,

A cell displays only one value at a time. If you want to display more than
one value you will need to put a formula in more than one cell. Is the
maximum repeats 2? So if there are 2 people whose name is Smith and the each
have 10 as their value and that is the max you want to display Smith, Smith?

=INDEX($C$4:$C$22,LARGE((MAX($E$4:$E$22)=$E$4:$E$2 2)*ROW($E$4:$E$22),ROW(A1))-3,1)

This formula must be array entered (press Shift Ctrl Enter to enter it)

Copy it down as far as you want. It will return multiple name, if there is
only one it will return VALUE error in all cells below the first one. You
can hide the VALUE errors by 1. making the formula far longer or 2. using
Conditional Formatting.
--
Cheers,
Shane Devenshire


"Sasikiran" wrote:

Hello,

I have a formula =INDEX(C4:C33,MATCH(MAX(E4:E33),E4:E33,0 which looks for
the maximum value in the range E4:E33 and displays the name of the person
which is equivalent in the range C4:C33.

C4:C33 has names and their respective values are there in range E4:E33.

The problem is if the max value is the same for two or more persons.

Please let me know how can the formula be changed that it shows all the
names if the max value is same for 2 or more persons.

Thanks in advance
Sasikiran

Problem with INDEX formula
 

Another way which is slightly shorter given that it won't return errors and
it will still return the correct values if you insert rows above the formula
and it also return the values in the order from top to bottom

=IF(ROWS(E$4:E4)<=SUM(N($E$4:$E$22=MAX($E$4:$E$22) )),INDEX($C$1:$C$22,SMALL(IF($E$4:$E$22=MAX($E$4:$ E$22),ROW($E$4:$E$22)-ROW($A$1)+1),ROWS($E$4:E4))),"")



--


Regards,


Peo Sjoblom




"Sasikiran" wrote in message
...
Hello,

I have a formula =INDEX(C4:C33,MATCH(MAX(E4:E33),E4:E33,0 which looks for
the maximum value in the range E4:E33 and displays the name of the person
which is equivalent in the range C4:C33.

C4:C33 has names and their respective values are there in range E4:E33.

The problem is if the max value is the same for two or more persons.

Please let me know how can the formula be changed that it shows all the
names if the max value is same for 2 or more persons.

Thanks in advance
Sasikiran