## Extract a list of duplicates from two columns combined using array formula in excel

*Article updated on December 12, 2010*

**Question:** I have two ranges or lists where I want to extract duplicates?

Answer:

### Excel 2007 formula in D2:

copied down to D20.

### Earlier Excel versions:

copied down to D20.

**Named ranges**

List1 (A2:A20)

List2 (B2:B7)

What is named ranges?

**How to implement array formula to your workbook**

Change named ranges. If your duplicates list starts at, for example, F3. Change D1:$D$1 in the above formulas to F2:$F$2.

Download excel sample file for this tutorial.

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

(Excel 2007 Workbook *.xlsx)

I have written a previous post about extracting duplicates from one column (range): How to extract a list of duplicates from a column in excel

**Functions in this article:**

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**IFERROR(**value;value_if_error**)** Returns value_if_error if expression is an error and the value of the expression itself otherwise

*This blog article is one out of six articles on the same subject.*

Extract a list of duplicates from a column using array formula in excel

Extract a list of duplicates from two columns combined using array formula in excel

Extract a list of duplicates from three columns combined using array formula in excel

Extract a list of alphabetically sorted duplicates from a column in excel

Filter duplicates from two columns combined and sort from A to Z using array formula in excel

Extract duplicates from a range using excel 2007 array formula

Extract a list of duplicates from three columns combined using array formula in excel

Question: I have three ranges or lists and I need to extract duplicates from those ranges combined using excel array […]Filter unique distinct and duplicate values from a large data set in excel 2007

In this post I am going to describe how to filter duplicate and unique distinct values from a really large […]### 2 Responses to “Extract a list of duplicates from two columns combined using array formula in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Hello,

I have duplicate data spread between C3 and W22. I would like to list the distinct values in C3:W22 in another worksheet(2003). could you please help me.

Regards,

Kamal

Hi Oscar,

I am having issues with the listing formula, I have a table as below:

S.no Month Date Session Name Session Duration Trainer

1 Sep 05-Sep-14 The Art of Tactful conversations 0.5 ABC

2 Sep 09-Sep-14 Managing Client Expectations 0.5 DEF

3 Sep 11-Sep-14 Creativity and Lateral Thinking 0.5 SBC

4 Sep 15 Sep14 and 16 Sep Shaping Customer Agenda 2 days ABC

5 Sep 16-Sep-14 The Art of Presentation Skills 0.5 SBC

6 Sep 23-Sep-14 Happiness @ Work 0.5 ABC

7 Sep 25-Sep-14 Emotional Intelligence 1 day DEF

8 Sep 29-Sep-14 Influencing and Negotiation skills 0.5 DEF

9 Sep 29-Sep-14 Planning and Prioritisation 0.5 SBC

10 Sep 30-Sep-14 Strengthening Workplace relations 0.5 ABC

I need to get a list of trainings done if i update a trainers name.

This has to be done in a seperate workbook.

Please help.

Thanks

DS