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 kth 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 an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a dropdown list and Conditional Formatting. There are [โฆ]
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
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in [โฆ]
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 [โฆ]
Populate drop down list with filtered Excel Table values
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated [โฆ]
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