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

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

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

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