This question already has an answer here:

- Excel Large Function with Duplicates 1 answer

So I have a column say A that looks like this:

```
I
15
0
3
15
M
8
0
8
21
Q
0
5
0
0
```

I can find the largest value with **=MAX(A1:A100)**
I can find the smallest value with **=SMALL(A1:A100,1)**

How do i find the one before the largest and the one before that. I can use Large to call the Nth value, but then how do I know how many N values the function counts so I can know what positions it uses refer to the last 3 (largest and the 2 before)?

**Ignoring duplicates - in the example the largest is 21, the one before is 15 and before that is 8.
If I do =LARGE(A1:A100,3) it will return 15 as there is two of em in the column. And I'm looking for the 8 as the third largest value.**

Below you will find the helpful folks of the Internet to have provided a PivotTable, an Array Formula solution, and I was finally able to do a simple formula one.

You can provide vba for the job should you insist we have everything, otherwise there is enough already.