Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Returning highest value
I have a list with value_name and its value. Is there a formula that can look down the list and pick the highest value and return the value_name For example _Value_Name_ _Value_ Value A 5 Value B 10 Value C 33 Value D 8 Value E 51 Value F 32 Value G 47 Value H 9 Value I 41 So the formula would return Value E as its result. Would be much appreciated :) -- Fishbone ------------------------------------------------------------------------ Fishbone's Profile: http://www.excelforum.com/member.php...o&userid=11848 View this thread: http://www.excelforum.com/showthread...hreadid=395276 |
#2
|
|||
|
|||
Assuming the data is in cells A2:B10, use the formula
=INDEX(A2:A10,MATCH(D2,B2:B10,0)) "Fishbone" wrote: I have a list with value_name and its value. Is there a formula that can look down the list and pick the highest value and return the value_name For example _Value_Name_ _Value_ Value A 5 Value B 10 Value C 33 Value D 8 Value E 51 Value F 32 Value G 47 Value H 9 Value I 41 So the formula would return Value E as its result. Would be much appreciated :) -- Fishbone ------------------------------------------------------------------------ Fishbone's Profile: http://www.excelforum.com/member.php...o&userid=11848 View this thread: http://www.excelforum.com/showthread...hreadid=395276 |
#3
|
|||
|
|||
Assuming that Column A and Column B contain your data, the following will return the 'value name' corresponding with the highest value, including the 'value name' of any ties for the highest value... C2, copied down: =RANK(B2,$B$2:$B$10)+COUNTIF($B$2:B2,B2)-1 D1: enter 1, indicating that you want a 'Top 1' list E1: =MAX(IF(B2:B10=INDEX(B2:B10,MATCH(D1,C2:C10,0)),C2 :C10))-D1 ...confirmed with CONTROL+SHIFT+ENTER F2, copied down: =IF(ROW()-ROW(F$2)+1<=$D$1+$E$1,INDEX(A$2:A$10,MATCH(ROW()-ROW(F$2)+1,$C$2:$C$10,0)),"") If you also want to return the corresponding 'Value', copy this formula over to the next column, Column G. Also, if for example you want a 'Top 5' list, change the 1 in D1 to a 5 and you will automatically get a Top 5 list, again, including any ties for 5th place. Hope this helps! Fishbone Wrote: I have a list with value_name and its value. Is there a formula that can look down the list and pick the highest value and return the value_name For example _Value_Name_ _Value_ Value A 5 Value B 10 Value C 33 Value D 8 Value E 51 Value F 32 Value G 47 Value H 9 Value I 41 So the formula would return Value E as its result. Would be much appreciated :) -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=395276 |
#4
|
|||
|
|||
Assuming the data is in cells A2:B10
You can use this Simple formula =INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10)) Your Freind Sanjeev Agarwal "Fishbone" wrote: I have a list with value_name and its value. Is there a formula that can look down the list and pick the highest value and return the value_name For example _Value_Name_ _Value_ Value A 5 Value B 10 Value C 33 Value D 8 Value E 51 Value F 32 Value G 47 Value H 9 Value I 41 So the formula would return Value E as its result. Would be much appreciated :) -- Fishbone ------------------------------------------------------------------------ Fishbone's Profile: http://www.excelforum.com/member.php...o&userid=11848 View this thread: http://www.excelforum.com/showthread...hreadid=395276 |
#5
|
|||
|
|||
I'm betting you put =max(b2:b10) in D2.
But you could have just used that in your formula: =INDEX(A2:A10,MATCH(max(b2:b10),B2:B10,0)) Duke Carey wrote: Assuming the data is in cells A2:B10, use the formula =INDEX(A2:A10,MATCH(D2,B2:B10,0)) "Fishbone" wrote: I have a list with value_name and its value. Is there a formula that can look down the list and pick the highest value and return the value_name For example _Value_Name_ _Value_ Value A 5 Value B 10 Value C 33 Value D 8 Value E 51 Value F 32 Value G 47 Value H 9 Value I 41 So the formula would return Value E as its result. Would be much appreciated :) -- Fishbone ------------------------------------------------------------------------ Fishbone's Profile: http://www.excelforum.com/member.php...o&userid=11848 View this thread: http://www.excelforum.com/showthread...hreadid=395276 -- Dave Peterson |
#6
|
|||
|
|||
And toss that de-caf stuff! <vbg
Duke Carey wrote: No, Dave. You gave me too much credit. I was writing the formula in the reply, on the fly, and forgot to put the MAX() function in. Somedays you just gotta drink 2 cups of coffee before giving advice. "Dave Peterson" wrote: I'm betting you put =max(b2:b10) in D2. But you could have just used that in your formula: =INDEX(A2:A10,MATCH(max(b2:b10),B2:B10,0)) Duke Carey wrote: Assuming the data is in cells A2:B10, use the formula =INDEX(A2:A10,MATCH(D2,B2:B10,0)) "Fishbone" wrote: I have a list with value_name and its value. Is there a formula that can look down the list and pick the highest value and return the value_name For example _Value_Name_ _Value_ Value A 5 Value B 10 Value C 33 Value D 8 Value E 51 Value F 32 Value G 47 Value H 9 Value I 41 So the formula would return Value E as its result. Would be much appreciated :) -- Fishbone ------------------------------------------------------------------------ Fishbone's Profile: http://www.excelforum.com/member.php...o&userid=11848 View this thread: http://www.excelforum.com/showthread...hreadid=395276 -- Dave Peterson -- Dave Peterson |
#7
|
|||
|
|||
It might be better to use :
=INDEX(A2:A10,MATCH(LARGE(B2:B10,1),B2:B10)) as it allows for the kth highest value's name to be displayed, by simply changing the number at LARGE(B2:B10,number). "Sanjeev" wrote: Assuming the data is in cells A2:B10 You can use this Simple formula =INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10)) Your Freind Sanjeev Agarwal "Fishbone" wrote: I have a list with value_name and its value. Is there a formula that can look down the list and pick the highest value and return the value_name For example _Value_Name_ _Value_ Value A 5 Value B 10 Value C 33 Value D 8 Value E 51 Value F 32 Value G 47 Value H 9 Value I 41 So the formula would return Value E as its result. Would be much appreciated :) -- Fishbone ------------------------------------------------------------------------ Fishbone's Profile: http://www.excelforum.com/member.php...o&userid=11848 View this thread: http://www.excelforum.com/showthread...hreadid=395276 |
#8
|
|||
|
|||
No, Dave. You gave me too much credit. I was writing the formula in the
reply, on the fly, and forgot to put the MAX() function in. Somedays you just gotta drink 2 cups of coffee before giving advice. "Dave Peterson" wrote: I'm betting you put =max(b2:b10) in D2. But you could have just used that in your formula: =INDEX(A2:A10,MATCH(max(b2:b10),B2:B10,0)) Duke Carey wrote: Assuming the data is in cells A2:B10, use the formula =INDEX(A2:A10,MATCH(D2,B2:B10,0)) "Fishbone" wrote: I have a list with value_name and its value. Is there a formula that can look down the list and pick the highest value and return the value_name For example _Value_Name_ _Value_ Value A 5 Value B 10 Value C 33 Value D 8 Value E 51 Value F 32 Value G 47 Value H 9 Value I 41 So the formula would return Value E as its result. Would be much appreciated :) -- Fishbone ------------------------------------------------------------------------ Fishbone's Profile: http://www.excelforum.com/member.php...o&userid=11848 View this thread: http://www.excelforum.com/showthread...hreadid=395276 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting the 2nd highest value in a row. | Excel Worksheet Functions | |||
3 highest values | New Users to Excel | |||
Highest Value / Circular Reference Help | Excel Discussion (Misc queries) | |||
Highest Value / Circular Reference Help | Excel Worksheet Functions | |||
In Excel, I need to now how to pick the second highest number in . | Excel Worksheet Functions |