Author: Oscar Cronquist Article last updated on December 04, 2018

Question:
How do I create a unique distinct list from a column sorted A to Z using array formula?

Array formula in D3:

=INDEX($B$3:$B$13, MATCH(MIN(IF(COUNTIF($D$2:D2, $B$3:$B$13)=0,COUNTIF($B$3:$B$13, "<"&$B$3:$B$13)+1,9.9999E+307)), COUNTIF($B$3:$B$13, "<"&$B$3:$B$13)+1, 0))

How to create an array formula

  1. Select cell D3.
  2. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  3. Press and hold Ctrl + Shift.
  4. Press Enter once.
  5. Release all keys.

How to copy this array formula

  1. Select cell D3.
  2. Copy (Ctrl + C) cell D2.
  3. Select D3:D8
  4. Paste (CTRL + V)

How this array formula works

Step 1 - Filter unique distinct values