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

Question: How do I extract a unique distinct list from a column containing blanks?

Answer: Cell range B3:B12 contains several blank cells. The following formula in cell D3 extracts unique distinct values from cell range B3:B12. Unique distinct values are all values except duplicates are merged into one distinct value.

Formula in D3:

=LOOKUP(2, 1/((COUNTIF($D$2:D2, $B$3:$B$12)=0)*($B$3:$B$12<>"")), $B$3:$B$12)

Copy cell B2 and paste to cells below.

Update 10th December 2020: Excel 365 subscribers can now use this regular formula in cell D3.

=UNIQUE(FILTER(B3:B12,B3:B12<>""))

There is no need to use absolute cell references with formulas that return a dynamic array, however, it is crucial that you use them with the first formula above, as shown.

Note that the formula above deploys an array of values to the appropriate cell range. If any of the cells below are populated cell D3 returns a #SPILL! error.

Check out how the formula works here: Extract unique distinct values ignoring blanks

Recommended article

Extract a unique distinct list sorted from A to Z

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Extract a unique distinct list sorted from A to Z

Explaining the LOOKUP formula in cell D3

Step 1 - Check cell range B3:B12 for non-empty cells

If a cell contains a value TRUE is returned. The following line is a logical expression, cells not equal to nothing return TRUE. The less and larger than characters are logical operators that evaluates to boolean values, True or False.

$B$3:$B$12<>""

becomes

{"AA";"AA";0;"BB";"CC";"DD";0;"BB";"EE";"EE"}<>""

and returns

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

Step 2 - Ignore duplicate cells

The COUNTIF function counts cells that equal a condition or any of the supplied criteria. The first argument has both an absolute and relative cell reference. This allows the cell range to grow when cell B3 is copied to cells below as far as needed.

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

becomes

COUNTIF("Unique distinct list",{"AA";"AA";0;"BB";"CC";"DD";0;"BB";"EE";"EE"})=0

becomes

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

The equal sign is also a logical operator like the less and greater signs, it evaluates tor True or False.

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

and returns

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

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

How to use the COUNTIF function

Step 3 - Multiply arrays

Multiplying boolean values is the same as applying OR logic to each value based on their position.

The first value in the first array is True and in the second array is also True. True * True equals 1.

The other possibilties are:

  • True * False = 0 (zero)
  • False * True = 0 (zero)
  • False * False = 0 (zero)

The boolean equivalent to True is 1 and False is 0 (zero).

(COUNTIF($D$2:D2, $B$3:$B$12)=0)*($B$3:$B$12<>"")

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}* {TRUE;TRUE; FALSE;TRUE; TRUE;TRUE; FALSE;TRUE; TRUE;TRUE}

and returns

{1; 1; 0; 1; 1; 1; 0; 1; 1; 1}

Step 4 - Divide 1 by the array

The reason I am dividing 1 with the array is to replace 0 (zero) with the #DIV/0. The LOOKUP function will ignore the #DIV/0 errors, shown in the next step.

1/((COUNTIF($D$2:D2, $B$3:$B$12)=0)*($B$3:$B$12<>""))

becomes

1/({1; 1; 0; 1; 1; 1; 0; 1; 1; 1})

and returns

{1; 1; #DIV/0!; 1; 1; 1; #DIV/0!; 1; 1; 1}

Step 5 - Find last match in array and return corresponding value

LOOKUP(2, 1/((COUNTIF($D$2:D2, $B$3:$B$12)=0)*($B$3:$B$12<>"")), $B$3:$B$12)

becomes

LOOKUP(2, {1;1;#DIV/0!;1;1;1;#DIV/0!;1;1;1}, $B$3:$B$12)

becomes

LOOKUP(2, {1;1;#DIV/0!;1;1;1;#DIV/0!;1;1;1}, {"AA";"AA"; 0;"BB";"CC"; "DD";0;"BB"; "EE";"EE"})

and returns "EE" in cell D3.

How to use the LOOKUP function

Finds a value in a sorted cell range and returns a value on the same row.

How to use the LOOKUP function

Recommended article

Extract a unique distinct list sorted from A to Z ignore blanks

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

Extract a unique distinct list sorted from A to Z ignore blanks