## Sort a range from A to Z [Array formula]

**Question: **How do I sort a range alphabetically using excel array formula?

**Answer:**

Cell rangeĀ $B$2:$E$5 contains text values in random order, the formula in cell B8 extracts values sorted from A to Z.

**Array formula in B8:**

Filter unique distinct values sorted from A to Z with no blanks from a multi-column and multi-row cell range:

Recommended articles

This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]

#### How to enter an array formula

- Double press with left mouse button on cell B8
- Copy an paste above formula
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once

Learn how to filter unique distinct values from a multi-column and multi-row cell range:

Recommended articles

This article demonstrates ways to list unique distinct values in a cell range with multiple columns. The data is not […]

#### How to implement array formula to your workbook

If your list starts at, for example, F2. Change $B$8:B8Ā in the above formula to F2:$F$2.

The following article demonstrates how to filter a unique distinct list sorted from A to Z, from a multi-column and multi-row cell range:

Recommended articles

The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range […]

#### How to copy array formula

Copy cell B8 and paste to cells below.

How to filter duplicate values from a multi-column and multi-row cell range:

Recommended articles

This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]

### Explaining formula in cell B8

#### Step 1 - Rank values based on their relative positionĀ if they were sorted

The COUNTIF function counts cells based on a condition, however, in this case, I am using a cell range instead of a single cell in the second argument.

Also, the ampersand & concatenates each value with a less than sign. This makes the COUNTIF function compare each value against the others, the number returned represents the corresponding values position if the list were sorted.

COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)

returnsĀ {1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1}.

The image above shows the array in cell range B7:E10, "Apple" is 0 (zero) which is the first position if the list were sorted.

#### Step 2 - Extract the k-th smallest value in array

The SMALL function lets you get the k-th smallest value from a cell range or array.

SMALL(array, k)

SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))

The ROWS function allows you to keep track of the number of cells, the cell reference is expanding as we copy the formula and paste to cells below.

SMALL({1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1}, ROWS($B$8:B8))

becomes

SMALL({1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1}, 1) and returns 0 (zero).

#### Step 3 - Compare value to array

The logical expression in the following IF function determines if the value of the position corresponding to the k-th smallest value will be a row number or FALSE.

IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

IF(0=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

IF(0={1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1}, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

IF({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

IF({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}, {1;2;3;4})

and returnsĀ {FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, **2**, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}.

#### Step 4 - Extract the smallest number from array

The MIN function ignores boolean values TRUE and FALSE.

MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1))

becomes

MIN({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, **2**, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE})

and returns 2. We now know that the first value to be extracted is located on row 2 relative to cell rangeĀ $B$2:$E$5.

#### Step 5 - Find column

In this case, the MATCH function returns the relative position horizontally of the value we are looking for.

MATCH(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8)),COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,MIN(IF(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8))=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)),,1)),0)

becomes

MATCH(SMALL({1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1},ROWS($B$8:B8)),COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,MIN(IF(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8))=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)),,1)),0)

becomes

MATCH(SMALL({1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1},1),COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,MIN(IF(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8))=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)),,1)),0)

becomes

MATCH(0,COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,MIN(IF(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8))=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)),,1)),0)

I will be using the INDEX function to extract the values we need based on a row number.

MATCH(0,COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,MIN(IF(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8))=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)),,1)),0)

becomes

MATCH(0,COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,2,,1)),0)

becomes

MATCH(0,COUNTIF($B$2:$E$5,"<"&B3:E3),0)

becomes

MATCH(0,{6,15,0,11},0)

and returns 3. The value we are looking for is in column 3 relative to cell rangeĀ $B$2:$E$5.

#### Step 6 - Return value based on row and column

INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0))

becomes

INDEX($B$2:$E$5, 2, 3)

and returns "Apple" in cell B8.

### Get Excel *.xlsx file

This post shows you how to filter a multi-column and multi-row cell range based on frequency:

Recommended articles

The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]

Recommended article

Recommended articles

Array formulas allows you to do advanced calculations not possible with regular formulas.

Recommended article

Recommended articles

I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]

*This blog article is one out of five articles on the same subject.*

Recommended articles

Recommended articles

Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]

Recommended articles

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

Recommended articles

### Sort values category

Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]

This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]

Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]

### Excel categories

### 5 Responses to “Sort a range from A to Z [Array formula]”

### 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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

[…] = window.adsbygoogle || []).push({}); Hi, I'm working on a simple macro that would insert a simple sorting formula depending on the user selection. I took few lines of code to call a form enabling the range […]

Hi, What if I want to sort alphabetically from B2 to E5 and keep the words within the range B2:E5? Thanks

how i sort a column through an excel formula

and can u explain about tbl in your formula because i did not get that

if u now about autofilter through a formula please give the formula

Hi

Thank you for all the info your providing

How do I create a formula to consider a range of numbers, sort them in descending order then add up only the 6 highest numbers?

yours truely

Markus

in this case "Sort a range from A to Z [Array formula]"

how about remove blank cell ?

thanks