How to find some LARGEST or SMALLEST numbers in a range

There are situations when we need to find out what is the highest or the lowest value in your table or any array of numbers. And sometimes we may need to determine what is the second or third highest or lowest value in a given range. In all such cases ARRAY formulas are very helpful, so I'm providing some examples of using them.

Let's look first at finding the highest/lowest values in an array/table of numbers. Here is a small sample range of numbers:

To extract the maximum and minimum values from this range we use this kind of array formulas:

=MAX(IF(ISERROR($B$2:$J$5),"",$B$2:$J$5))        Answer: 53

=MIN(IF(ISERROR($B$2:$J$5),"",$B$2:$J$5))         Answer: -9

If we want to find out also what are the second, third, etc... largest/smallest values within the range, we need to use different array formulas as shown in the following examples.

For 2nd largest:  =LARGE(IF(ISERROR($B$2:$J$5),"",$B$2:$J$5),2)   Answer: 50

For 3rd largest:   =LARGE(IF(ISERROR($B$2:$J$5),"",$B$2:$J$5),3)   Answer: 47,

and so on.

For 2nd smallest: =SMALL(IF(ISERROR($B$2:$J$5),"",$B$2:$J$5),2)   Answer: -8

For 3rd smallest:  =SMALL(IF(ISERROR($B$2:$J$5),"",$B$2:$J$5),3)   Answer: -7,

and so on. 

To make the formulas much more flexible we can use cell references instead of 2, 3 (marked by bolded pink in the formulas above).

Couple important notes related to those formulas:

1. The formulas are of ARRAY type so, after typing in, you have to enter them using CTRL+SHIFT+ENTER keyboard sequence.

2. Even if there are some strings included in the tested range the formulas will provide correct results.

3. If there is any empty cell within the range of positive values only, you will get 0 result as the minimum value, so be careful.

4. If there are multiple largest or smallest numbers in the range, the formulas will find just the first one. To find out, if there are more than one max/min values, you'd need to use Find feature provided in Excel ribbon.