Author: Oscar Cronquist Article last updated on August 23, 2021

This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want in your duplicates list.

For example, column B contains values, column D contains exceptions. The formula is in cell F3, it extracts all duplicates from column B except values specified in column D.

The formula in cell E2 returns duplicate values from column A but values in column C are excluded from the list.

## 1. Extract duplicate values with exceptions

This example demonstrates a formula for earlier versions, Excel 2019, and previous versions. I recommend the smaller formula demonstrated in section 2 if you use Excel 365.

Array formula in cell F3:

=IFERROR(LOOKUP(2, 1/((COUNTIF(\$B\$3:\$B\$21, \$B\$3:\$B\$21)>1)*(COUNTIF(\$F\$2:F2, \$B\$3:\$B\$21)=0)*(COUNTIF(\$D\$3:\$D\$4, \$B\$3:\$B\$21)<>1)), \$B\$3:\$B\$21), "")

### 1.1 How to enter an array formula

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

The formula now begins with and ends with a curly bracket, this is Excel letting you know the formula is an array formula.

Don't enter these characters yourself, they appear automatically.

### 1.2 Explaining formula in cell F2

#### Step 1 -

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

#### Step 2 -

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

#### Step 3 -

COUNTIF(\$D\$3:\$D\$4, \$B\$3:\$B\$21)<>1

#### Step 4 -

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

#### Step 5 -

1/((COUNTIF(\$B\$3:\$B\$21, \$B\$3:\$B\$21)>1)*(COUNTIF(\$F\$2:F2, \$B\$3:\$B\$21)=0)*(COUNTIF(\$D\$3:\$D\$4, \$B\$3:\$B\$21)<>1))

#### Step 6 -

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

#### Step 7 -

IFERROR(LOOKUP(2, 1/((COUNTIF(\$B\$3:\$B\$21, \$B\$3:\$B\$21)>1)*(COUNTIF(\$F\$2:F2, \$B\$3:\$B\$21)=0)*(COUNTIF(\$D\$3:\$D\$4, \$B\$3:\$B\$21)<>1)), \$B\$3:\$B\$21), "")

## 2. Extract duplicate values with exceptions - Excel 365

The following formula is a dynamic array formula and is entered as a regular formula, it works only in Excel 365.

Formula in cell F3:

=LET(z, FILTER(B3:B21, (COUNTIF(B3:B21, B3:B21)>1)*NOT(COUNTIF(D3:D4, B3:B21))), FILTER(z, MATCH(z, z, 0)<>SEQUENCE(ROWS(z))))

### 2.1 Explaining array formula in cell F2

#### Step 1 - Check if the value is a duplicate

The COUNTIF function counts values based on a condition. It can also be used to count multiple values.

COUNTIF(B3:B21, B3:B21)>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 ";"Federer, Roger ";"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 ";"Federer, Roger ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "})>1

becomes

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

The larger than character checks if the numbers in the array is larger than 1. The output is TRUE or FALSE.

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

returns {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}.

#### Step 2 - Check if the value is in the exceptions list

This step returns an array that shows if the values are in the exceptions list or not.

COUNTIF(D3:D4, B3:B21)

becomes

COUNTIF({"Federer, Roger ";"Gonzalez, Fernando "},{"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 ";"Federer, Roger ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "})

and returns {1; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0}.

#### Step 3 - Convert True to False

The NOT function converts True to False and False to True. The numerical equivalent to TRUE is 1 and False is 0 (zero).

NOT(COUNTIF(D3:D4, B3:B21))

becomes

NOT({1; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0})

and returns {FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.

#### Step 4 - Apply AND logic

The asterisk lets you multiply the arrays. It returns TRUE only if both arrays contain TRUE.

TRUE * TRUE = TRUE (1)
FALSE* TRUE = FALSE (0)
TRUE * FALSE= FALSE (0)
FALSE * FALSE= FALSE (0)

Note that the boolean values are convertted into their numerical equivalents, TRUE = 1, and FALSE = 0 (zero).

(COUNTIF(B3:B21, B3:B21)>1)*NOT(COUNTIF(D3:D4, B3:B21))

becomes

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

and returns {0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0}.

#### Step 5 - Filter values

The FILTER function lets you extract values/rows based on a condition or criteria. It is in the Lookup and reference category and is only available to Excel 365 subscribers.

FILTER(B3:B21, (COUNTIF(B3:B21, B3:B21)>1)*NOT(COUNTIF(D3:D4, B3:B21)))

becomes

FILTER(B3:B21, {0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0})

becomes

FILTER({"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 "; "Federer, Roger "; "Nalbandian, David "; "Robredo, Tommy "; "Wawrinka, Stanislas "; "Cilic, Marin "; "Stepanek, Radek "; "Almagro, Nicolas "}, {0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0})

and returns {"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}.

#### Step 6 - Save formula to value

The LET function assigns names to calculation results, this can reduce a formula significantly.

LET(z, FILTER(B3:B21, (COUNTIF(B3:B21, B3:B21)>1)*NOT(COUNTIF(D3:D4, B3:B21))), formula)

FILTER(B3:B21, (COUNTIF(B3:B21, B3:B21)>1)*NOT(COUNTIF(D3:D4, B3:B21))) is assigned to z.

z is {"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}.

#### Step 7 - Evaluate formula in LET function

FILTER(z, MATCH(z, z, 0)<>SEQUENCE(ROWS(z)))

becomes

FILTER({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, MATCH({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, {"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, 0)<>SEQUENCE(ROWS({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "})))

becomes

FILTER({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, MATCH({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, {"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, 0)<>SEQUENCE(4))

becomes

FILTER({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, MATCH({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, {"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, 0)<>{1;2;3;4})

becomes

FILTER({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, {1;1;3;3}<>{1;2;3;4})

becomes

FILTER({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}, {FALSE;TRUE;FALSE;TRUE})

and returns {"Davydenko, Nikolay ";"Wawrinka, Stanislas "}.