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

https://www.youtube.com/watch?v=GE8Z_oSDuoc

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)), "")

Copy cell C2 and paste it down to D20.

Learn more:

How to use the IFERROR function

If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function […]

How to use the IFERROR function

Earlier Excel versions, array formula in C2:

=IF(ISERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))), "", INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0)))

The IFERROR function was introduced in Excel 2007, if you have an earlier version then use the formula above.

How (the old) array formula works

Step 1 - Show a duplicate value only once

=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))

COUNTIF(D2:$D$2, $B$3:$B$21) contains both a relative and absolute reference (D2:$D$2) to a range.

When you copy a cell reference like this the cell reference expands.

COUNTIF(D2:$D$2, $B$3:$B$21) returns this array in cell C2: (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

How to use the COUNTIF function

Step 2 - Filter values in $A$2:$A$20 having duplicates

=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))

COUNTIF($B$3:$B$21, $B$3:$B$21) counts the number of cells within a range that meet the given condition. The returning array is (2, 1, 1, 2, 1, 1, 2, 2, 1, 2, 2, 1, 2, 1, 1, 2, 1, 1, 1, 1).

COUNTIF($B$3:$B$21, $B$3:$B$21)>1 returns this array: (TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE).

IF(COUNTIF($B$3:$B$21, $B$3:$B$21)>1, 0, 1) returns (1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0).

How to use the IF function

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

How to use the IF function

Step 3 - Calculate arrays combined

=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))

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

(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0) + (1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0) equals

(1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0)

Step 4 - Identify duplicates

=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))

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

MATCH(0, (1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0), 0) is 2.

Match returns the relative position of an item in an array that matches a specified value

How to use the MATCH function

Identify the position of a value in an array.

How to use the MATCH function

Step 5 - Return duplicates

=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))

becomes

=INDEX($B$3:$B$21, 2) is Federer, Roger.

Index returns a value or reference of the cell at the intersection of a particular row and column, in a given range

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

How to use the INDEX function

Final thoughts

When formula in c2 is copied to c3 the reference changes.

Example

The formula in c2: =INDEX($A$2:$A$20, MATCH(0, COUNTIF(D2:$D$2, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))

Then copy the formula to C3.

The formula references changes: =INDEX($A$2:$A$20, MATCH(0, COUNTIF(D3:$D$2, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))

Read more about absolute and relative cell references.

This makes it possible to avoid previous cell values (C2) and only calculate the remaining values.

Download Excel file


* You will also get a weekly newsletter, unsubscribe anytime!