Use the value of one cell to return another

Hello all,

I am trying to list the top 4 people based on a score. For
hypothetical lets just say that this is my table: Two columns A and B

A B

Frank 7
Cindy 12
John 6
Sam 3

Now, I would like column C to return the NAME of the people(all o
them) with the person having the highest score listed first(C1 would b
first place, C2 second, etc). How would this be done? Thank you fo
your time

--
Thada
-----------------------------------------------------------------------
Thadar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2250
View this thread: http://www.excelforum.com/showthread.php?threadid=49700

Bob
Fri Dec 30 13:31:57 CST 2005

in C1:
=INDEX($A$1:$A$100,MATCH(LARGE(B$1:B$100,ROW()),B$1:B$100,0))
and fill down

Bob Umlas
Excel MVP

"Thadar" <Thadar.20v9ln_1135968901.3229@excelforum-nospam.com> wrote in
message news:Thadar.20v9ln_1135968901.3229@excelforum-nospam.com...
>
> Hello all,
>
> I am trying to list the top 4 people based on a score. For a
> hypothetical lets just say that this is my table: Two columns A and B
>
> A B
>
> Frank 7
> Cindy 12
> John 6
> Sam 3
>
> Now, I would like column C to return the NAME of the people(all of
> them) with the person having the highest score listed first(C1 would be
> first place, C2 second, etc). How would this be done? Thank you for
> your time.
>
>
> --
> Thadar
> ------------------------------------------------------------------------
> Thadar's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=22506
> View this thread: http://www.excelforum.com/showthread.php?threadid=497005
>

Bernard
Fri Dec 30 13:39:52 CST 2005

Assuming your data is in A1:B4 use
=INDEX($A$1:$A$4,MATCH(LARGE($B$1:$B$4,ROW(A1)),$B$1:$B$4,0))
in C1 and copy down to C4
If the data is elsewhere, change $A$1:$A$4 and $B1:$B4 as needed but leave
ROW(A1) as is - it gives us LARGE(range,1) which becomes LARGE(range,2) when
copied down a row
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Thadar" <Thadar.20v9ln_1135968901.3229@excelforum-nospam.com> wrote in
message news:Thadar.20v9ln_1135968901.3229@excelforum-nospam.com...
>
> Hello all,
>
> I am trying to list the top 4 people based on a score. For a
> hypothetical lets just say that this is my table: Two columns A and B
>
> A B
>
> Frank 7
> Cindy 12
> John 6
> Sam 3
>
> Now, I would like column C to return the NAME of the people(all of
> them) with the person having the highest score listed first(C1 would be
> first place, C2 second, etc). How would this be done? Thank you for
> your time.
>
>
> --
> Thadar
> ------------------------------------------------------------------------
> Thadar's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=22506
> View this thread: http://www.excelforum.com/showthread.php?threadid=497005
>

Dirk
Fri Dec 30 14:00:07 CST 2005


"Thadar" <Thadar.20v9ln_1135968901.3229@excelforum-nospam.com> wrote in message
news:Thadar.20v9ln_1135968901.3229@excelforum-nospam.com...
>
> Hello all,
>
> I am trying to list the top 4 people based on a score. For a
> hypothetical lets just say that this is my table: Two columns A and B
>
> A B
>
> Frank 7
> Cindy 12
> John 6
> Sam 3
>
> Now, I would like column C to return the NAME of the people(all of
> them) with the person having the highest score listed first(C1 would be
> first place, C2 second, etc). How would this be done? Thank you for
> your time.

1) Insert a column A with the rank of the numbers in column C
by filling cell A1 with the formula
= rank( c1, c:c )
and copy downward.

2) Fill cell D1 with the formula
=VLOOKUP( ROW(D1), A$1:B$4, 2, FALSE )
and copy downward.

3) If you don't want to see the ranking column, hide it.

Dirk Vdm

Dirk
Fri Dec 30 14:10:28 CST 2005


"Bob Umlas" <Excel_Trickster@msn.com> wrote in message news:OlhD1eXDGHA.2292@tk2msftngp13.phx.gbl...
> in C1:
> =INDEX($A$1:$A$100,MATCH(LARGE(B$1:B$100,ROW()),B$1:B$100,0))
> and fill down

Yes, that's a nice one-column solution.
Even shorter and easier to handle:
= INDEX( A:A, MATCH( LARGE( B:B, ROW() ), B:B, 0 ) )

Dirk Vdm

Peo
Fri Dec 30 14:47:41 CST 2005

Won't work if there are ties,

Frank 7
Cindy 12
John 6
Sam 6

will return

Cindy
Frank
John
John




I'd suggest

http://www.cpearson.com/excel/rank.htm

--
Regards,

Peo Sjoblom

(No private emails please)


"Bob Umlas" <Excel_Trickster@msn.com> wrote in message
news:OlhD1eXDGHA.2292@tk2msftngp13.phx.gbl...
> in C1:
> =INDEX($A$1:$A$100,MATCH(LARGE(B$1:B$100,ROW()),B$1:B$100,0))
> and fill down
>
> Bob Umlas
> Excel MVP
>
> "Thadar" <Thadar.20v9ln_1135968901.3229@excelforum-nospam.com> wrote in
> message news:Thadar.20v9ln_1135968901.3229@excelforum-nospam.com...
>>
>> Hello all,
>>
>> I am trying to list the top 4 people based on a score. For a
>> hypothetical lets just say that this is my table: Two columns A and B
>>
>> A B
>>
>> Frank 7
>> Cindy 12
>> John 6
>> Sam 3
>>
>> Now, I would like column C to return the NAME of the people(all of
>> them) with the person having the highest score listed first(C1 would be
>> first place, C2 second, etc). How would this be done? Thank you for
>> your time.
>>
>>
>> --
>> Thadar
>> ------------------------------------------------------------------------
>> Thadar's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=22506
>> View this thread:
>> http://www.excelforum.com/showthread.php?threadid=497005
>>
>
>

Dirk
Fri Dec 30 15:55:56 CST 2005


"Peo Sjoblom" <terre08@mvps.org> wrote in message news:ui7KRJYDGHA.2320@TK2MSFTNGP11.phx.gbl...
> Won't work if there are ties,
>
> Frank 7
> Cindy 12
> John 6
> Sam 6
>
> will return
>
> Cindy
> Frank
> John
> John

If you add 0.00001*ROW( ) to each value of the B-column,
there can't be any ties :-)

Dirk Vdm