Table of contents

  1. How to sort a table by Column 1 and then by Column 2  (array formula)
  2. 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:

=INDEX(Descr, MATCH(SMALL(COUNTIF(Descr, "<"&Descr), ROW(1:1)), COUNTIF(Descr, "<"&Descr), 0)) + CTRL + SHIFT + ENTER

copied down as far as needed.

Array formula in F2:

=INDEX(Qty, MATCH(SMALL(COUNTIF(Descr, "<"&Descr)+(1/(COUNTIF(Qty, ">"&Qty)+1)), ROW(1:1)), COUNTIF(Descr, "<"&Descr)+(1/(COUNTIF(Qty, ">"&Qty)+1)), 0)) + CTRL + SHIFT + ENTER

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

Comments(0) Filed in category: Excel, Records, Sort values

Sort values in a table (vba)

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

Comments(7) Filed in category: Excel, Sort values

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

Comments(1) Filed in category: Excel, Excel table, Sort values

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

Comments(3) Filed in category: Excel, Sort values

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...)

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

External resources:

Sorting arrays in parallel (VBA)