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

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

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

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

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

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

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

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