## Sort values in parallel (array formula)

*Article updated on August 21, 2017*

**Table of contents**

- How to sort a table by Column 1 and then by Column 2 (array formula)
- How to sort a table by Column 1 and then by Column 2 (Right-click / Sort /Custom sort...)

### 1. How to sort a table by Column 1 and then by Column 2 (array formula)

Array formula in E2:

copied down as far as needed.

Array formula in F2:

copied down as far as needed.

Recommended articles

How to sort a table in a custom order in excel [No formula]

Your boss wants you to sort the company´s products by a new criterion, quality. You receive a list from your […]

Sort values in an Excel table [VBA]

awall asks: Hey can you do the opposite of this - not random order but this is my situation. i […]

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

Sort a range from A to Z using array formula in excel

Question: How do I sort a range alphabetically using excel array formula? Answer: Array formula in B8: =INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), […]

**Named ranges**

Descr (B2:B15)

Qty (B2:B15)

What is named ranges?

### Download excel sample file for this tutorial

Array formula sorts values in parallel.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

**ROW(**reference**)** returns the rownumber of a reference

**MATCH(**lookup_value;lookup_array; [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

### 2. How to sort a table by Column 1 and then by Column 2 (Right-click / Sort /Custom sort...)

- Select range (B2:B15)
- Right click and select sort
- Click Custom sort..
- Sort by "Description" and values from A to Z
- Then by "Qty" and values from smallest to largest
- Click ok.

**External resources:**

Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Sort text cells alphabetically from two columns using excel array formula

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]### 16 Responses to “Sort values in parallel (array formula)”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Oscar,

In the part of the formula: 1/(COUNTIF(Qty,">"&Qty)+1))

What is the purpose or logic for divide 1 by the countif (Qty,....)

and

what is the reason for the +1 par of the same formula?

thanks,

Chrisham

chrisham,

Sorry for the late answer.

What is the purpose or logic for divide 1 by the countif (Qty,....)?The purpose is to sort and find the right "Qty" value for each "Description" value. I know this is a short answer but use "Evaluate Formula" to see each step in formula calculation.

what is the reason for the +1 par of the same formula?COUNTIF(Qty, ">"&Qty) returns an array containing a zero. 1/0 returns an error. So I had to add 1 to the whole array.

If there is information in adjacent columns, say:

Joel, AA, 7

Mark, FF, 31

Nick, AA, 7

with the possibility of matching sort criteria (columns 2 & 3), how would I display the corresponding data after the sort (sorting only columns 2 & 3) without repeats; that is,

Joel, AA, 7

Nick, AA, 7

Mark, FF, 31

Instead of:

Joel, AA, 7

Joel, AA, 7

Mark, FF, 31

?

Ralee,

read this post: Sort values in parallel in excel, part 2

Oscar, can you help me understand the logic behind comparing the array values to themselves? Descr, "<"&Descr

Is there an article I can read somewhere?

Kris,

COUNTIF(Descr, "<"&Descr) compares values in an array and ranks them on their order if they were sorted. COUNTIF({"FF";"BB";"GG";"EE";"AA";"GG";"FF";"BB";"EE";"BB";"HH";"EE";"AA";"GG"}, "<"&{"FF";"BB";"GG";"EE";"AA";"GG";"FF";"BB";"EE";"BB";"HH";"EE";"AA";"GG"}) COUNTIF({"FF"; "BB"; "GG"; "EE"; "AA"; "GG"; "FF"; "BB"; "EE"; "BB"; "HH"; "EE"; "AA"; "GG"}, {; "<"&"BB"; "<"&"GG"; "<"&"EE"; "<"&"AA"; "<"&"GG"; "<"&"FF"; "<"&"BB"; "<"&"EE"; "<"&"BB"; "<"&"HH"; "<"&"EE"; "<"&"AA"; "<"&"GG"}) FF, first value in array becomes: {"FF"&"<"&"FF"; "BB"&"<"&"FF"; "GG"&"<"&"FF"; "EE"&"<"&"FF"; "AA"&"<"&"FF"; "GG"&"<"&"FF"; "FF"&"<"&"FF"; "BB"&"<"&"FF"; "EE"&"<"&"FF"; "BB"&"<"&"FF"; "HH"&"<"&"FF"; "EE"&"<"&"FF"; "AA"&"<"&"FF"; "GG"&"<"&"FF"} becomes {0;1;0;1;1;0;0;1;1;1;0;1;1;0} and the total is 8. COUNTIF(Descr, "<"&Descr) returns {8;2;10;5;0;10;8;2;5;2;13;5;0;10}

I'm new to excel array techniques (and loving it), so please forgive me if my question's solution is obvious, but your tip goes a long way toward answering it.

I need to perform an approximate match upon an unsorted table and then lookup an associated value. Because the unsorted table is dynamic and will gain many new rows over time, I need the solution to be formula-based, with no intermediate tables.

Is there a way to build upon this tip to have the sorted arrays in memory? My current strategy would be to use LOOKUP vector function upon the two like-sorted arrays. A MrExcel/ExcelIsFun video demonstrates a related solution (see link below), but the table values are numeric and are sorted directly using SMALL() which won't work upon text data.

Many Thanks!

Mr Excel & excelisfun Trick 36: VLOOKUP w Approximate Match & Unsorted Table

https://www.youtube.com/watch?v=rxhL72gvM5E

Markosys,

That´s a question I don´t have an answer to. It seems overly complicated to create a 2dimensional sorted array when you can use MATCH function to lookup values in an unsorted array.

=INDEX(array, MATCH(lookup_value, array, 0))

Dear Oscar,

I need array formula if there space empty row in the part of rows 2 until 15, say row 5 and row 10 . so the formula generate exactly the same as your Example.

Please help me on this.

and how can i Sort values Parallel with 4 (four) column or more.

Sergio,

Download example file:Sergio.xls

great one i love it.one small one i required has u had given a xls to sergio in that if there is an empty in any one error is generated like #NUM!. but me want empty space instead of #NUM!. pls help pls

pardhu,

Excel 2007:

=IFERROR(INDEX(Descr, MATCH(SMALL(IF(Descr="", "", COUNTIF(Descr, "<"&Descr)), ROW(1:1)), IF(Descr="", "", COUNTIF(Descr, "<"&Descr)), 0)), "") Excel 2003: =IF(ISERROR(INDEX(Descr, MATCH(SMALL(IF(Descr="", "", COUNTIF(Descr, "<"&Descr)), ROW(1:1)), IF(Descr="", "", COUNTIF(Descr, "<"&Descr)), 0))), "", INDEX(Descr, MATCH(SMALL(IF(Descr="", "", COUNTIF(Descr, "<"&Descr)), ROW(1:1)), IF(Descr="", "", COUNTIF(Descr, "<"&Descr)), 0)))

THANK YOU SO MUCH SIR .......... YOUR GREAT

i am very new ing advanced excel. i want to sort from largest to small, but i can't. i need your guidence. Thanx.

Uygar,

Sort from smallest to largest numberFormula

=SMALL($A$2:$A$11, ROW(A1))

Sort from smallest to largest text lengthArray formula

=INDEX($A$2:$A$9, MATCH(SMALL(LEN($A$2:$A$9), ROW(A1)), IF(COUNTIF($C$1:C1, $A$2:$A$9)=COUNTIF($A$2:$A$9, $A$2:$A$9), "", LEN($A$2:$A$9)), 0))

How to create an array formula1. Select cell C2

2. Paste formula

3. Press and hold Ctrl + Shift

4. Press Enter

Download excel *.xlsx file

Sort-from-smallest-to-largest.xlsx