Use the value of one cell to return another

[Thadar]

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 containing the name, B containing the score.

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.

[Bob Umlas]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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...o&userid=22506
> View this thread: http://www.excelforum.com/showthread...hreadid=497005
>

[Bernard Liengme]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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...o&userid=22506
> View this thread: http://www.excelforum.com/showthread...hreadid=497005
>

[Dirk Van de moortel]

"Thadar" <[email protected]> wrote in message
news:[email protected]...
>
> 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 Van de moortel]

"Bob Umlas" <[email protected]> wrote in message news:[email protected]...
> 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 Sjoblom]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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...o&userid=22506
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=497005
>>
>
>

[Dirk Van de moortel]

"Peo Sjoblom" <[email protected]> wrote in message news:[email protected]...
> 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