## 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 formula?

**Answer:**

### Excel 2007 array formula in D2:

copied down to D20.

**Named ranges**

List1 (A2:A20)

List2 (B2:B7)

List3 (C2:C8)

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-three-columns-in-excel.xlsx

(Excel 2007 Workbook *.xlsx)

**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

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 […]Extract a list of duplicates from two columns combined using array formula in excel

Question: I have two ranges or lists where I want to extract duplicates? Answer: Excel 2007 formula in D2: =IFERROR(IFERROR(INDEX(List1, […]### 7 Responses to “Extract a list of duplicates from three columns combined using array formula in excel”

### Leave a Reply

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

<code>your formula</code>

**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

This is close to what I need. I have three lists of email addresses. If an email address appears in all three (not two out of three) lists then place it in the duplicate column. Also I need all three named ranges to be dynamic. Can you help?

Peter Voss,

See this post: Filter values that exists in all three lists

[...] in Compare, Excel, Search/Lookup on Sep.27, 2012. Email This article to a Friend Peter Voss asks:This is close to what I need. I have three lists of email addresses. If an email address appears in [...]

Hi there,

I am using Excel 2011 on a Mac and keep getting errrors...

Vanessa,

I don´t have a mac, perhaps it is possible to disable error checking rules:

https://www.addictivetips.com/microsoft-office/show-error-on-formula-referring-to-an-empty-cell-in-excel-2010/

I said before that you're a genius

But I want the previous version (office 2003)

I said before that you're a genius

But I want the previous formula (office 2003)