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

Question: How do i remove common values between two lists?

Answer: The solution in this article, removes common values and the original values, as seen on the picture above.

Array formula in cell F3:

=IFERROR(INDEX(\$B\$3:\$B\$12, SMALL(IF(COUNTIF(\$D\$3:\$D\$12,\$B\$3:\$B\$12)=0, MATCH(ROW(\$B\$3:\$B\$12),ROW(\$B\$3:\$B\$12)), ""), ROWS(\$A\$1:A1))), INDEX(\$D\$3:\$D\$12, SMALL(IF(COUNTIF(\$B\$3:\$B\$12,\$D\$3:\$D\$12)=0, MATCH(ROW(\$D\$3:\$D\$12),ROW(\$D\$3:\$D\$12)), ""), ROWS(\$A\$1:A1)-SUM((COUNTIF(\$D\$3:\$D\$12, \$B\$3:\$B\$12)=0)+0))))

To enter an array formula, type the formula in cell B3 then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully.

Don't enter the curly brackets yourself, they appear automatically.

### Explaining formula in cell F3

#### Step 1 - Count values

The COUNTIF function lets you count values based on a condition or criteria.Â COUNTIF(range,Â criteria) The first argument is the cell range you want to evaluate.

The criteria argument contains the values you want to look for.

COUNTIF(\$D\$3:\$D\$12,\$B\$3:\$B\$12)

becomes

COUNTIF({"AA"; "BB"; "VV"; "DD"; "EE"; "FF"; "GG"; "HH"; "II"; "JJ"},{"AA"; "BB"; "CC"; "DD"; "EE"; "PP"; "GG"; "HH"; "II"; "JJ"})

and returns the following array {1;1;0;1;1;0;1;1;1;1}.

The image to the right shows the array and the corresponding values, you can now easily see that CC and PP are not found in List 2.

#### Step 2 - Check if value is 0 (zero) and if so return the corresponding row number

If the array contains a zero the corresponding value is not found in the other list. The IF function lets you build a logical expression, if it evaluatesÂ to TRUE on thing happens and if FALSE another thing happens.

IF(COUNTIF(\$D\$3:\$D\$12, \$B\$3:\$B\$12)=0, MATCH(ROW(\$B\$3:\$B\$12),ROW(\$B\$3:\$B\$12)), "")

becomes

IF({1;1;0;1;1;0;1;1;1;1}, {1;2;3;4;5;6;7;8;9;10}, "")

and returns the following array:

{"";"";3;"";"";6;"";"";"";""}

The image to the right shows the row numbers (relative to cell rangeÂ \$B\$3:\$B\$12) in column C .

#### Step 3 - Extract the k-th smallest row number

The SMALL function returns the k-thÂ smallest number from a cell range or an array. This allows us to extract a single value in a cell each.

SMALL(IF(COUNTIF(\$D\$3:\$D\$12, \$B\$3:\$B\$12)=0, MATCH(ROW(\$B\$3:\$B\$12),ROW(\$B\$3:\$B\$12)), ""), ROWS(\$A\$1:A1))

becomes

SMALL({"";"";3;"";"";6;"";"";"";""}, ROWS(\$A\$1:A1))

becomes

SMALL({"";"";3;"";"";6;"";"";"";""}, 1)

and returns 3.

#### Step 4 - Get the corresponding value

The INDEX function returns a value based on a row and column number, the column number is not necessaryÂ if the cell range only has one column.

INDEX(\$B\$3:\$B\$12, SMALL(IF(COUNTIF(\$D\$3:\$D\$12, \$B\$3:\$B\$12)=0, MATCH(ROW(\$B\$3:\$B\$12),ROW(\$B\$3:\$B\$12)), ""), ROWS(\$A\$1:A1)))

becomes

INDEX(\$B\$3:\$B\$12, 3)

and returns the value in cell B5 which is "CC" to cell F3.

#### Step 5 - Return values from List 2

When there are no more values to extract from List 1 the IFERROR function lets you continue with List 2.

The formula is repeated in the second argument except that other cell ranges are used. I am not going to explain these steps again, see above steps if you need to.

The fomula in cell F5 becomes

IFERROR(INDEX(\$B\$3:\$B\$12, SMALL(IF(COUNTIF(\$D\$3:\$D\$12, \$B\$3:\$B\$12)=0,ROW(\$B\$3:\$B\$12)-MIN(ROW(\$B\$3:\$B\$12))+1,""),ROW(B1))),INDEX(\$D\$3:\$D\$12, SMALL(IF(COUNTIF(\$B\$3:\$B\$12,\$D\$3:\$D\$12)=0, ROW(\$D\$3:\$D\$12)-MIN(ROW(\$D\$3:\$D\$12))+1, ""), ROW(B1)-SUM((COUNTIF(\$D\$3:\$D\$12,\$B\$3:\$B\$12)=0)+0))))

becomes

IFERROR(#NUM,INDEX(\$D\$3:\$D\$12, SMALL(IF(COUNTIF(\$B\$3:\$B\$12,\$D\$3:\$D\$12)=0, ROW(\$D\$3:\$D\$12)-MIN(ROW(\$D\$3:\$D\$12))+1, ""), ROW(B1)-SUM((COUNTIF(\$D\$3:\$D\$12,\$B\$3:\$B\$12)=0)+0))))

becomes

=IFERROR(#NUM,INDEX(\$D\$3:\$D\$12, 3))

and returns "VV" in cell F5.