# Second highest value

I know how to use MAX(A1:A30) to get the highest value in the cell range A1 to A30.

But how do I get the second or third highest value?

The following formula, will allow you to choose the highest value you want:

``````=INDEX(SORT(UNIQUE(A1:A30),1,FALSE),1,0)
``````

The formula explained from the inside out:

• The `SORT` function will sort the range `DESCENDING` via the `FALSE` statement (high to low). The `1` indicates the first column.
• The `UNIQUE` function will exclude any duplicates
• The `INDEX` function will allow you to select the order you want: `1` for the first, `2` for the second....and so on.

I was about to write a script and found out that the problem can be resolved by good old formulas !!

Try looking for the `SMALL` and `LARGE` formula.

I'd use:

``````=LARGE(UNIQUE(A1:A30);2)
``````

`UNIQUE` to pick and look only for the different values in the range. The number `2` is for the 2nd largest number.

I know I'm late but if anyone wants to get the 2nd Highest Value treating ties as the same rank then the following will work:

``````=LARGE(A1:A30,COUNTIF(A1:A30,MAX(A1:A30))+1)
``````