INDEX and MATCH, I think

Hi List,

I have a column of 20 names in A
A column of numbers, 1 - 20 in B
A column of 20 RAND() in C

I then sort B on C results

I want to display the name that now corresponds to 1 in D1

I hope someone can help. I have read and tried but..!

TIA,

John

=INDEX($A$1:$A$20, MATCH(D1, $B$1:$B$20, 0))

But I wonder if this does what you really want:

=INDEX($A$1:$A$20, RANK(D1, $C$1:$C$20))

Or enter the following formula into in E1 and copy E1 into E2:E20:

=INDEX($A$1:$A$20, RANK(C1, $C$1:$C$20))

The latter provides in E1:E20 a random list of the names in A1:A20.

=INDEX(A1:A20,MATCH(1,B1:B20,0)) works.
Thanks for your help.

John

Originally Posted by JohnHedge

I tried your first suggestion by putting
=INDEX($A$1:$A$20, MATCH(D1, $B$1:$B$20, 0)) in D1
It produced a circular argument error message.

I never said to put the formula into D1.

And in hindsight, I guess I needed to tell you __not__ to put the formula into D1.

FYI, that is __always__ the case, unless you are purposely relying on the circular reference. But that is not a good idea, IMHO.

So __never__ put a formula into a cell that the formula references.