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

A unique value is a value that only exists once in a list.

Unique list

A unique distinct list contains all cell values but duplicates are merged into one distinct cell value.

Unique distinct list

If your are looking for a unique distinct list array formula, see this blog article:
Create a unique distinct alphabetically sorted list

The following array formula extracts unique values from column B in cell D3 and below:

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

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Explaining formula in cell D3

Step 1 - Identify unique values

The COUNTIF function counts cells in cell range based on a condition or criteria. If the value is equal to 1 then it must be a unique value.

COUNTIF($B$3:$B$21, $B$3:$B$21)=1