Excel Large Function with Duplicates

Question

I'm trying to apply a LARGE function to get the 5 largest values from a column ignoring duplicates. I did some searching around and found some proposed solutions but they don't seem to work for me. In my actual application the range is found by an array formula so I'm not sure if non array formulas will work here. My original function was {=LARGE(IF($A:$A=$A2,$B:$B),2)} dragged down the column. Replace 2 with 3 for the third largest, 4 for the fourth largest, etc. This worked except in the case of duplicates.

For example:

The data is in column 1 and the desired output is in column 2:

1    5 
2    4
2    3
3    2 
3    1
3
4
4
5

Things I have tried:

1. {=LARGE(IF(A:A < LARGE(A:A,i-1),A:A),1)} for the i-th largest value.

   -Inputting this gives 3 for both i = 3 and i = 4.

2. =LARGE(A:A,COUNTIF(A:A,MAX(A:A))+1)

   -This doesn't seem to be any different than just LARGE(A:A,2)

3. {=MAX(IF(A:A < LOOKUP(9.99999999999999E+307,A:A),A:A))}

   -This does retrieve the value 4 but I'm not sure how to generalize it to the kth largest value.

VBA solutions are fine as well. I tried to code one myself but wasn't sure how to code in the array formula IF($A:$A=$A2,$B:$B).

Solution

In B1 enter:

=LARGE($A$1:$A$9,1)

In B2 enter the Array Formula:

=MAX(IF(A$1:A$9<B1,A$1:A$9))

and copy down. Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Basically, we exclude previously found items from the Large() / Max() range.

Comments

[learningAsIGo]

Amazing! I had to use an array formula for the initial large function as well since my range was defined using an IF() but everything seems to be good. Took a while to compute but I suppose that comes with the array function territory. Might you have some insight into why the formulas I put in my original post failed for my usage case?

[Gary's Student]

@learningAsIGo Your equations had the right idea, but did not cascade properly.

[learningAsIGo]

I'm not sure what you mean by cascade. Do you mean my array formulas would have to reference the previous value somehow instead of just copying down the column?