Author: Oscar Cronquist Article last updated on February 28, 2019

The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed in column C.

Update 2017-08-19! New regular formula in cell D3:

=LOOKUP(2,1/(((COUNTIF($F$2:F2, $B$3:$B$21)=0)*(COUNTIF($B$3:$B$21, $B$3:$B$21)>1))), $B$3:$B$21)

This video explains how to use the formula and how it works

The following formula is an outdated formula, the above formula is smaller and better.

Array formula in D3:

=INDEX($B$3:$B$21, MATCH(0, COUNTIF(D2:$D$2, $B$3:$B$21)+IF(COUNTIF($B$3:$B$21, $B$3:$B$21)>1, 0, 1), 0))

How to enter an array formula

  1. Copy (Ctrl + c) above formula
  2. Double click on cell C2
  3. Paste (Ctrl + v) to cell C2
  4. Press and hold CTRL + SHIFT simultaneously
  5. Press Enter once
  6. Release all keys

Your formula now looks like this: {=array_formula}
Don't enter the curly brackets, they appear automatically.

How to enter an array formula

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

How to enter an array formula

How to copy the formula

Copy cell C2 and paste it to cells below as far as needed.

Remove #num errors:

=IFERROR(INDEX($B$3:$B$21, MATCH(0, COUNTIF(D2:$D$2, $B$3:$B$21)+IF(COUNTIF($B$3:$B$21, $B$3:$B$21)>1, 0, 1), 0)), "")