## SMALL function with multiple cell ranges

*Article last 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:

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

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

Extract largest values from two columns

Question: How do I extract the five largest values from two or more columns? Answer: Formula in A12: =INDEX(tbl, SMALL(IF(LARGE(tbl,ROW(A1))=tbl, […]

SMALL function with duplicates

The formulas in column E, shown in the picture above, extracts the k-th smallest value from B3:B9 ignoring the duplicate numbers. […]

The array formula in column E, shown in above picture sorts text values from column B. The Length columns prove […]

How to ignore zeros using the SMALL function

The formula in cell D3 is an array formula, it will extract the k-th smallest value ignoring zeros. Related articles

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form