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

The following regular formula extracts duplicate values from column B (List1), D (List2) and F (List3) combined, the result is displayed in cell H3 and cells below.

Formula in cell H3:

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

Explaining formula in cell F3

This formula consists of three similar parts, one returns values from List1 and the second returns values from List2 and the third returns duplicates from List3.

IFERROR(IFERROR(formula1, formula2), formula3)

Step 1 - Prevent duplicate values in output

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 not returned.

COUNTIF($H$2:H2,$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 in List1.

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($H$2:H2,$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($H$2:H2,$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($H$2:H2,$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, 1/{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 H3.

Step 4 - 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.

Another IFERROR function is used to handle errors from List2, the formula then returns values from List.

IFERROR(IFERROR(formula1, formula2), formula3)

Download Excel *.xlsx file

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

Related articles