Author: Oscar Cronquist Article last updated on December 09, 2020

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique distinct values are all values except duplicates.

Example, in column B value "DD" exists twice in cell B3 and B11. In column D value "DD" exists only once since it is a unique distinct list.

Update 2020-12-09, the formula below is for Excel 365 subscribers:

=UNIQUE(SORT($B$3:$B$11))

You can find an explanation here: Extract unique distinct values sorted from A to Z and other examples here: How to use the UNIQUE function

Use the array formula below if you own an earlier Excel version.

Array formula in cell D3:

=INDEX($B$3:$B$11, MATCH(SMALL(IF(COUNTIF($D$2:D2, $B$3:$B$11)=0, COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), ""), 1), COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), 0))

Watch a video that explains how to use it and how it works:

Learn how to filter values with a condition and return unique distinct values sorted from A to Z:

Unique distinct list sorted alphabetically based on a condition

This article demonstrates how to extract unique distinct values based on a condition and also sorted from A to z. […]

Unique distinct list sorted alphabetically based on a condition

 

How to create an array formula

  1. Double press with left mouse button on cell D3
  2. Copy (Ctrl +c) amd paste (Ctrl+v) above formula to cell D3
  3. Press and hold Ctrl + Shift simultaneously
  4. Press Enter once
  5. Release all keys

The formula in the formula bar should now look like this: {=formula}
Don't enter the curly brackets yourself, they appear automatically.

A beginners guide to Excel array formulas

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

A beginners guide to Excel array formulas

How to copy array formula

  1. Select cell D3
  2. Copy cell  (Keyboard shortcut: Ctrl + c)
  3. Select cell range D4:D7
  4. Paste (Keyboard shortcut: Ctrl + v)

Explaining array formula in cell

Step 1 - Identify values not yet shown above current cell

The COUNTIF function counts the number of times a value exists in a cell range.

Cell range $D$2:D2 changes as the formula is copied down to cells below. This makes it possible to avoid duplicate values in the list.

COUNTIF($D$2:D2,$B$3:$B$11)=0

becomes

COUNTIF("Unique list sorted alphabetically",{"DD";"EE";"FF";"EE";"GG";"BB";"FF";"GG";"DD"})=0

becomes

{0;0;0;0;0;0;0;0;0}=0

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

Step 2 - Create an array with a ranking sort number

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

becomes

{1;3;5;3;7;0;5;7;1}

Step 3 - Convert array with not displayed values to an array containing rank numbers

The IF function converts not yet displayed values into alphabeically ranked numbers.

IF(COUNTIF($D$2:D2,$B$3:$B$11)=0,COUNTIF($B$3:$B$11,"<"&$B$3:$B$11),"")

becomes

IF({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE},{1;3;5;3;7;0;5;7;1},"")

and returns

{1;3;5;3;7;0;5;7;1}

Step 4 - Find the smallest value in array

The SMALL function extracts the smallest number in the array.

SMALL(IF(COUNTIF($D$2:D2, $B$3:$B$11)=0, COUNTIF($B$3:$B$11,"<"&$B$3:$B$11), ""),1)

becomes

SMALL({1;3;5;3;7;0;5;7;1},1)

and returns 0.

How to use the SMALL function

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.

How to use the SMALL function

Step 5 - Find relative position in the array

The MATCH function finds the position of the next alphabetically sorted value.

MATCH(SMALL(IF(COUNTIF($D$2:D2, $B$3:$B$11)=0, COUNTIF($B$3:$B$11,"<"&$B$3:$B$11),""), 1),COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), 0)

becomes

MATCH(0,COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), 0)

becomes

MATCH(0,{1;3;5;3;7;0;5;7;1}, 0)

and returns 6.

Step 6 - Return value in data based on row coordinate

The INDEX function returns a value based on row and column number.

INDEX($B$3:$B$11, MATCH(SMALL(IF(COUNTIF($D$2:D2, $B$3:$B$11)=0, COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), ""), 1), COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), 0))

becomes

INDEX($B$3:$B$11, 6)

becomes

INDEX({"DD"; "EE"; "FF"; "EE"; "GG"; "BB"; "FF"; "GG"; "DD"}, 6)

and returns BB in cell D3.

Get excel *.xlsx file

Unique distinct list sorted alphabetically.xlsx