finding n-th most frequently occuring word

[Matt Stayton]

I am trying to find the n-th most frequently occurring name is a database. I
have a formula that finds the top most frequently occurring name
=INDEX(rng,MATCH(LARGE(UNIQUEVALUES(COUNTIF(rng,rng)),2),COUNTIF(rng,rng),0))
this being entered as an array.

How would I find the second, third, fourth, etc. most frequently occurring
name?

Example:

George
Ilysha
Steven
George
Steven
George

Thanks!

 

[Matt Stayton]

Sorry, i entered the incorrect array formula, my formula for the top most
frequent is ACTUALLY

=INDEX(rng,MATCH(MAX(COUNTIF(rng,rng)),COUNTIF(rng),0))

 

[T. Valko]

Try these array formulas** :

For the most frequent item:

=INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0)))

Assume that formula is entered in cell C1.

For the nth most frequent items in descending order, entered in cell C2 and
copied down as needed:

=INDEX(A$1:A$20,MODE(IF(COUNTIF(C$1:C1,A$1:A$20)=0,MATCH(A$1:A$20,A$1:A$20,0)+{0,0})))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Teethless mama]

In B1: =INDEX(rng,MODE(MATCH(rng,rng,0)+{0,0}))
In B2: =INDEX(rng,MODE(IF(COUNTIF($B$1:B1,rng)=0,MATCH(rng,rng,0)+{0,0})))

Ctrl+Shift+Enter on both formula listed above, and copy from B2 down as far
as needed.

 

[Matt Stayton]

With these formulas my cells say #N/A.

what may i be entering incorrectly?