Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index formula problem | Excel Worksheet Functions | |||
INDEX problem with cells of same value | Excel Worksheet Functions | |||
Index/Match problem | Excel Worksheet Functions | |||
INDEX PROBLEM...I THINK | Excel Worksheet Functions | |||
INDEX problem | Excel Worksheet Functions |