Return nth value from list

[Matt]

Hi -

I have a list of values in range b3:b100. I'd like to
return the nth value to cell a2. I was going to use the
rank function in range a3:a100 and then do a vlookup in
cell a2 that looks for the nth rank and returns the value
from col b. That works, but I'd prefer to do the entire
formula in cell a2 - without adding the rank formulas.
Any ideas?

I'm using Excel 97 in Windows 2000. Thanks!

 

[Bob Phillips]

Would

=LARGE(B3:B100,n)

work for you - changing n to suit


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Norman Harker]

Hi Matt!

Take a look at the LARGE and SMALL functions:

eg:
LARGE
Returns the kth largest value in a data set
=LARGE(array,k)

 

[Peo Sjoblom]

Try something like

=INDEX(B1:B100,MATCH(C1,RANK(B1:B100,B1:B100),0))

entered with ctrl + shift & enter

where C1 is contains the nth rank (hard coded if you are looking for 10th
rank would be)

=INDEX(B1:B100,MATCH(10,RANK(B1:B100,B1:B100),0))


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Harlan Grove]

=INDEX(B1:B100,MATCH(10,RANK(B1:B100,B1:B100),0))

....

If there were, say, 3 entries tied for 9th place, this would return #N/A
while LARGE(B1:B100,10) would return that tying value. Something for the OP
to consider.

 

[Peo Sjoblom]

I agree, it was brain freeze on my part

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)