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
Related posts:
Extract a list of duplicates from two columns combined using array formula in excel
Filter duplicates from two columns combined and sort from A to Z using array formula in excel
Extract a list of duplicates from a column using array formula
Extract a unique distinct list from two columns using excel 2007 array formula
Recommended categories
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:
http://www.addictivetips.com/microsoft-office/show-error-on-formula-referring-to-an-empty-cell-in-excel-2010/