http://tinyurl.com/2cvlpd

Biff

Previously I learned the following

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),N),COUNTIF(rng,"<"&rng),0))
array formula  à ascending order

=INDEX(rng,MATCH(LARGE(COUNTIF(rng,"<"&rng),N),COUNTIF(rng,"<"&rng),0))
array formula à descending order

I use them to retrieve the Nth element in a data set of numbers or text.

They work fine if there are no duplicates.

Tonight I have discovered a formula created by David Hager which takes care
of duplicates.  But it is only good for numbers.

=SMALL(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N)  array
formula  --> ascending order

=LARGE(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N)  array
formula -->  descending order

Don't know if I have missed anything.  Biff, I will put them away in my
stash although I can't think of an example why one would need to retrieve
the Nth element.

Comments welcome from all.

Why do things the hard way?

For numbers I can just use

=SMALL(rng,N)

This is good if there are no duplicates.

Previously I learned the following

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),N),COUNTIF(rng,"<"&rng),0))  array formula  à ascending order

=INDEX(rng,MATCH(LARGE(COUNTIF(rng,"<"&rng),N),COUNTIF(rng,"<"&rng),0))  array formula à descending order

I use them to retrieve the Nth element in a data set of numbers or text.

They work fine if there are no duplicates.

Tonight I have discovered a formula created by David Hager which takes care of duplicates.  But it is only good for numbers.

=SMALL(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N)  array formula  --> ascending order

=LARGE(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N)  array formula -->  descending order

Don't know if I have missed anything.  Biff, I will put them away in my stash although I can't think of an example why one would need to retrieve the Nth element.

Comments welcome from all.

Thanks for posting this, it is just what I was looking for.

To answer your question why it is useful, right now I've got a lookup table
at work which lists physician specialties (which are text and duplicated),
effective dates, and amounts.  The last column of the table lists the unique,
nonblank physician specialty descriptions which, in turn, is used by an input
cell (on a different sheet) that has data validation applied (with the list
option) so that the user can choose the specialty from a drop down box.  A
lookup formula uses the selected specialty description and date to return the
proper amount from the table.  When additional items are added to the table,
the inputs for the drop down box will automatically update to include the new
specialties.  One other benefit is it eliminates typos (by the user - not the
person updating the table)  that would result in the specialty description
not being found in the table since the drop down is fed from the same table
used to do the lookup.

But, I would like the drop down options in alphabetical order to make it
easier to find the one you are looking for.  

Thanks for posting this, it is just what I was looking for.

To answer your question why it is useful, right now I've got a lookup table
at work which lists physician specialties (which are text and duplicated),
effective dates, and amounts.  The last column of the table lists the unique,
nonblank physician specialty descriptions which, in turn, is used by an input
cell (on a different sheet) that has data validation applied (with the list
option) so that the user can choose the specialty from a drop down box.  A
lookup formula uses the selected specialty description and date to return the
proper amount from the table.  When additional items are added to the table,
the inputs for the drop down box will automatically update to include the new
specialties.  One other benefit is it eliminates typos (by the user - not the
person updating the table)  that would result in the specialty description
not being found in the table since the drop down is fed from the same table
used to do the lookup.

But, I would like the drop down options in alphabetical order to make it
easier to find the one you are looking for.