**Return Values which are Duplicates in 2 Columns ie. which are Common in 2 Columns**

**Related Links:**

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

**Extract Duplicates / Non-Duplicates in 2 Columns - which are Common / Not-Common in 2 Columns**

**Column D**: List all values which are common (Duplicates) in both columns A & B - covers Duplicates in BOTH columns & NOT within Column;

**Column E**: List all values which are common (Duplicates) in both columns A & B OR within same column;

**Column F**: List all values which are NOT common (Non-Duplicates) in columns A & B, irrespective of values being repeated within same column or not;

**Column G**: List all values which are NOT common (Non-Duplicates) in columns A & B, and list values ONLY if Non-Duplicates (ie. not repeated) within same column;

Enter non-array formula in cell D2 / F2 / G2 and copy down. Enter Array formula (Ctrl+Shift+Enter) in cell E2 and copy down.

**Note**: If cell D2 formula is in a different column, replace "D$1:D1" in the formula with that column (ex. if formula is in column J then update to "J$1:J1") and if cell D2 formula starts from a different cell, say D16 instead of D2, then replace "D$1:D1" with "D$15:D15"; do similarly for cell E2 / F2 / G2 formulas;

-----------------------------------------------------------------------------------------------

**List all values which are common (Duplicates) in both columns A & B - covers Duplicates in BOTH columns & NOT within Column.**

**Open Image in New Tab" for a full and clear view.**

**Cell D2 formula:**

** **

**=IFERROR(INDEX(A$2:A$20, MATCH(0, INDEX( COUNTIF(D$1:D1, A$2:A$20)+ (COUNTIF( B$2:B$20, A$2:A$20)=0),), 0)), "")**

**Explanation - Cell D2 formula (non-Array formula):**

1) COUNTIF(D$1:D1, A$2:A$20): counts values of column A in the preceding cells of the formula column (column D) as the formula gets copied down. It returns an array of 1s and zeros, with 1 indicating column A values which already occur in these preceding cells.

2) COUNTIF(B$2:B$20, A$2:A$20): returns an array of values comprising of numbers and zeros - {0;0;2; 0;0;1; 0;0;0; 1;0;0; 0;0;0; 0;0; 0;0} - the numbers indicate the number of times each column A cell value appears in column B. Zeros indicate blank cells or if a column A value does not appear in column B. For example, the 1st number 2 in 3rd position indicates that cell A4 (3rd cell) value of "CCC" appears twice in column B (in cells B10 & B12), the next number 1 in 6th position indicates that cell A7 (6th cell) value of "FFF" appears once in column B (ie. cell B8), and so on. Equating with zero - "COUNTIF( B$2:B$20, A$2:A$20)=0" - returns TRUE for column A values which do not appear in column B while FALSE indicates column A values which also appear in column B, and when used in calculation TRUE evaluates to the number 1, and FALSE evaluates to the number 0.

3) COUNTIF(D$1:D1, A$2:A$20)+ (COUNTIF( B$2:B$20, A$2:A$20)=0): returns an array of zeros and 1s - {1;1;0; 1;1;0;1; 1;1;0;1; 1;1; 1;1; 1;1;1;1} - where zeros indicate non-blank cells of column A with values which also appear in column B and which do not appear in the preceding cells of the formula column (column D). INDEX is used with this to make it a non-array formula. This is matched with zero (lookup_value) to return position of cells with these values and then using "INDEX(A$2:A$20" returns the values from column A which also appear (ie. which are common) in column B.

**Column E**: List all values which are common (Duplicates) in both columns A & B OR within same column. This uses INDEX, MATCH and COUNTIF functions and is an Array formula (Ctrl+Shift+Enter). This also refers to previous cells in the column (similar to cell D2 formula) - refer the abovementioned Note - if cell D2 formula is in a different column, replace "D$1:D1" in the formula with that column.

**Column F**: List all values which are NOT common (Non-Duplicates) in columns A & B, irrespective of values being repeated within same column or not. This uses INDEX, MATCH and COUNTIF functions and is a Non-Array formula. This also refers to previous cells in the column (similar to cell D2 formula) - refer the abovementioned Note - if cell D2 formula is in a different column, replace "D$1:D1" in the formula with that column.

**Column G**: List all values which are NOT common (Non-Duplicates) in columns A & B, and list values ONLY if Non-Duplicates (ie. not repeated) within same column. This uses INDEX, MATCH and COUNTIF functions and is a Non-Array formula. This also refers to previous cells in the column (similar to cell D2 formula) - refer the abovementioned Note - if cell D2 formula is in a different column, replace "D$1:D1" in the formula with that column.

**Only One of the Multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas**.