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
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

 

[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

 

[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

 

[Dirk Van de moortel]

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]

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)

 

[Dirk Van de moortel]

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