Use the value of one cell to return another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Thadar - 30 Dec 2005 18:54 GMT

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

Reply to this Message

Bob Umlas - 30 Dec 2005 19:31 GMT

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

> Hello all,
>
[quoted text clipped - 12 lines]
> first place, C2 second, etc). How would this be done? Thank you for
> your time.

Reply to this Message

Dirk Van de moortel - 30 Dec 2005 20:10 GMT

> 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

Reply to this Message

Peo Sjoblom - 30 Dec 2005 20:47 GMT

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

Signature

Regards,

Peo Sjoblom

(No private emails please)

> in C1:
> =INDEX($A$1:$A$100,MATCH(LARGE(B$1:B$100,ROW()),B$1:B$100,0))
[quoted text clipped - 23 lines]
>> View this thread:
>> http://www.excelforum.com/showthread.php?threadid=497005

Reply to this Message

Dirk Van de moortel - 30 Dec 2005 21:55 GMT

> Won't work if there are ties,
>
[quoted text clipped - 9 lines]
> John
> John

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

Dirk Vdm

Reply to this Message

Bernard Liengme - 30 Dec 2005 19:39 GMT

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

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Hello all,
>
[quoted text clipped - 12 lines]
> first place, C2 second, etc). How would this be done? Thank you for
> your time.

Reply to this Message

Dirk Van de moortel - 30 Dec 2005 20:00 GMT

> Hello all,
>
[quoted text clipped - 12 lines]
> 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

Reply to this Message