Article updated on February 16, 2018

Today I learned how to sort numbers from multiple cell ranges thanks to Sam Miller. It is surprisingly simple and easy.

Formula in cell H3:

=SMALL(($B$3:$B$8, $D$3:$D$6, $F$3:$F$7), ROWS($A$1:A1))

The SMALL function ignores text and blank cells, however, not error values.

Explaining formula in cell H3

The first argument in the SMALL function is the array parameter: SMALL(array, k).

Use parentheses to enable multiple cell ranges in the first argument.

($B$3:$B$8, $D$3:$D$6, $F$3:$F$7)

The , (comma) separates the cell references.

The second argument allows you to specify which number to extract based on their sort order.

ROWS($A$1:A1)

The ROWS function allows you to insert new rows and columns in your worksheet without breaking the formula.

The cell reference contains two parts, one is an absolute cell reference and the other is a relative cell reference.

The $ sign allows you to specify an absolute cell reference, this cell reference does not change when you copy the formula to cells below.

Download Excel *.xlsx file

SMALL function with multiple cell ranges.xlsx