## Sort a table with an array formula

The sorted table to the right is created with an array formula using the data in the table to the left.

**Array formula in cell F3:**

Recommended article

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

or use this regular formula:

**Formula in cell F3:**

**How to create an array formula**

- Copy array formula
- Select cell F3
- Click in formula bar

- Paste array formula to formula bar
- Press and hold CTRL + SHIFT
- Press Enter

Recommended article

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

**How to copy array formula **

- Select cell F3
- Copy cell (Ctrl +c)
- Select cell range G:3:H3
- Paste (Ctrl + v)
- Select cell range F3:H3
- Copy (Ctrl + c)
- Select cell range F4:H8
- Paste (Ctrl + v)

### How does this array formula work?

**Step 1 - Sort Col B from A to Z with COUNTIF function**

COUNTIF($B$3:$B$8, "<"&$B$3:$B$8)

becomes

COUNTIF({"A";"A";"A";"B";"B";"C"},{"<A";"<A";"<A";"<B";"<B";"<C"})

and returns

{0;0;0;3;3;5}

Recommended article

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

How to use the COUNTIF function

**Step 2 - Sort Col C from A to Z with COUNTIF function**

COUNTIF(C3:C8,"<"&C3:C8)/10

becomes

COUNTIF({"CC";"AA";"CC";"EE";"CC";"CC"},"<"&{"CC";"AA";"CC";"EE";"CC";"CC"})/10

and returns

{0.1;0;0.1;0.5;0.1;0.1}

**Step 3 - Sort Col D from A to Z with COUNTIF function**

COUNTIF(D3:D8,"<"&D3:D8)/100

becomes

COUNTIF({"BBB";"DDD";"AAA";"AAA";"BBB";"AAA"},{"<BBB";"<DDD";"<AAA";"<AAA";"<BBB";"<AAA"})/100

and returns

{0.03;0.05;0;0;0.03;0}

**Step 4 - Add all values**

COUNTIF($B$3:$B$8, "<"&$B$3:$B$8)+COUNTIF($C$3:$C$8, "<"&$C$3:$C$8)/10+COUNTIF($D$3:$D$8, "<"&$D$3:$D$8)/100

becomes

{0;0;0;3;3;5} + {0.1;0;0.1;0.5;0.1;0.1} + {0.03;0.05;0;0;0.03;0}

and returns

{0.13;0.05;0.1;3.5;3.13;5.1}

**Step 5 - Return the k-th smallest value**

SMALL(array, k)

SMALL(COUNTIF($B$3:$B$8, "<"&$B$3:$B$8)+COUNTIF($C$3:$C$8, "<"&$C$3:$C$8)/10+COUNTIF($D$3:$D$8, "<"&$D$3:$D$8)/100, ROW(A1))

becomes

SMALL({0.13;0.05;0.1;3.5;3.13;5.1}, ROW(A1))

becomes

SMALL({0.13;0.05;0.1;3.5;3.13;5.1}, 1)

and returns 0.05

Recommended article

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.

**Step 6 - Find the relative position**

MATCH(SMALL(COUNTIF($B$3:$B$8, "<"&$B$3:$B$8)+COUNTIF($C$3:$C$8, "<"&$C$3:$C$8)/10+COUNTIF($D$3:$D$8, "<"&$D$3:$D$8)/100, ROW(A1)), COUNTIF($B$3:$B$8, "<"&$B$3:$B$8)+COUNTIF($C$3:$C$8, "<"&$C$3:$C$8)/10+COUNTIF($D$3:$D$8, "<"&$D$3:$D$8)/100, 0)

becomes

MATCH(0.05, {0.13;0.05;0.1;3.5;3.13;5.1}, 0)

and returns 2.

Recommended article

Identify the position of a value in an array.

**Step 7 - Return the corresponding value**

=INDEX($B$3:$D$8, MATCH(SMALL(COUNTIF($B$3:$B$8, "<"&$B$3:$B$8)+COUNTIF($C$3:$C$8, "<"&$C$3:$C$8)/10+COUNTIF($D$3:$D$8, "<"&$D$3:$D$8)/100, ROW(A1)), COUNTIF($B$3:$B$8, "<"&$B$3:$B$8)+COUNTIF($C$3:$C$8, "<"&$C$3:$C$8)/10+COUNTIF($D$3:$D$8, "<"&$D$3:$D$8)/100, 0), COLUMN(A1))

becomes

=INDEX($B$3:$D$8, 2, COLUMN(A1))

becomes

=INDEX($B$3:$D$8, 2, 1)

becomes

=INDEX({"A";"A";"A";"B";"B";"C"}, 2, 1)

and returns A in cell F3.

Recommended article

Gets a value in a specific cell range based on a row and column number.

### Download excel *.xlsx file

Recommended article

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 […]

How to use a Table name in Data Validation Lists and Conditional Formatting formulas

David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]

Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

Count unique distinct values in a filtered Excel defined Table

A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]

Remove common records between two data sets

This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]

Cyril asks how to hide all columns of a range except columns whose header is found in specific cells. I am […]

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

Create a unique distinct sorted list containing both numbers text removing blanks

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

Sort dates within a date range

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 […]

The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]

Lookup and return multiple values sorted in a custom order

Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to […]

### One Response to “Sort a table with an 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

Ola,

Gostaria de fazer uma classificação de pares e impares para jogos aqui no Brasil.

Assim:

de c3 até q3000 quinze dezenas (numeros)

de U3 em diante separar Impares e Pares

Desde ja agradeço

Rogerio