Author: Oscar Cronquist Article last updated on November 20, 2018

The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in column F.

Formula in cell F3:

=IFERROR(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), LOOKUP(2, 1/((COUNTIF($F$2:F2, $D$3:$D$8)=0)*((COUNTIF($D$3:$D$8, $D$3:$D$8)>1)+(COUNTIF($B$3:$B$21, $D$3:$D$8)>0))), $D$3:$D$8))

Explaining formula in cell F3

This formula consists of two similar parts, one returns values from List1 and the other returns values from List2.

IFERROR(formula1, formula2)

Step 1 - Prevent duplicate values

The COUNTIF function counts values based on a condition, in this case, I am counting values in cells above. This makes sure that duplicates are ignored.

COUNTIF($F$2:F2,$B$3:$B$21)=0

becomes

COUNTIF("Duplicates",$B$3:$B$21)=0

becomes

COUNTIF("Duplicates",{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "})=0

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

and returns

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

Step 2 - Count values in List1

We want to know where the duplicates are if there are any.

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

becomes

COUNTIF({"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "},{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "})>1

becomes

{2;1;1;2;1;1;2;2;1;2;2;2;1;1;1;2;1;1;1}>1

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

Step 3 - Multiply arrays

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

becomes

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

and returns

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

Step 4 - Divide 1 with array

The LOOKUP function ignores error and if we divide 1 with 0 an error occurs. 1/0 = #DIV/0!

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

becomes

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

and returns

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

Step 5 - Return value based on array

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)

becomes

LOOKUP(2, 1/{1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0}, $B$3:$B$21)

becomes

LOOKUP(2, 11/{1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0}, {"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "})

and returns Wawrinka, Stanislas in cell F3.

Step 6 - Return values from List2

When values run out from List1 formula1 returns errors, the IFERROR function then moves to formula2.

IFERROR(formula1, formula2)

formula2 is just like formula1 except that it returns values from List2 and duplicates found between List1 and List2.

Earlier Excel versions:

=IF(ISERROR(INDEX(List1, MATCH(0, COUNTIF(D1:$D$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0))), INDEX(List2, MATCH(0, COUNTIF(D1:$D$1, List2)+IF((COUNTIF(List2, List2)+COUNTIF(List1, List2))>1, 0, 1), 0)), INDEX(List1, MATCH(0, COUNTIF(D1:$D$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0))) + CTRL + SHIFT + ENTER

Get Excel *.xlsx

how-to-extract-a-list of duplicates from two columns-in-excelv2.xlsx

Weekly Blog EMAIL

[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Extract a list of duplicates from a column

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

Create a date range [Formula]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

Filter duplicate values using criteria

This article demonstrates formulas that extract duplicates based on three conditions. Table of Contents Filter duplicate values using criteria How […]

Filter duplicate values in a range using “contain” condition

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]

Create a list with most recent data available

Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]

Highlight duplicate values in a cell range

The following conditional formula highlights only the second instance or more of a value in a cell range. Conditional formatting […]