Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct list? Merge two list without duplicates, in other words.

unique-distinct-list-from-two-columns1

Answer:

unique-distinct-list-from-two-columns2

Excel 2007 and later versions

The IFERROR function was introduced in Excel 2007.

Array formula in C2:

=IFERROR(IFERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$20), 0)), INDEX($B$2:$B$7, MATCH(0, COUNTIF($C$1:C1, $B$2:$B$7), 0))), "")

Recommended articles

Extract a unique distinct list sorted alphabetically removing blanks from a range

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

Comments(21) Filed in category: Excel, Sorted unique distinct values

Extract a unique distinct list sorted from A-Z from range in excel

Inspired from a comment in this article Unique values from multiple columns using array formulas I have now created this […]

Comments(3) Filed in category: Excel, Sorted unique distinct values, Unique distinct values

Unique distinct values from multiple columns using array formula

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Comments(23) Filed in category: Excel, Unique distinct values

Extract duplicates from a range using excel array formula

Overview Inspired by a comment in this post Unique values from multiple columns using array formulas, I  created an array […]

Comments(0) Filed in category: Duplicate values, Excel

Extract a unique distinct list from three columns

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

Comments(31) Filed in category: Excel, Unique distinct values

How to create an array formula

  1. Select cell C2
  2. Click in formula bar
    formula bar
  3. Paste array formula to formula bar
  4. Press and hold Ctrl+ Shift
  5. Press Enter
  6. Release all keys

Recommended article

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Built-in features, Count values, Excel

How to copy array formula

  1. Copy cell C2
  2. Select cell range C3:C19
  3. Paste

Earlier versions of Excel

Array formula in cell C2:

=IF(ISERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$20), 0))), INDEX($B$2:$B$7, MATCH(0, COUNTIF($C$1:C1, $B$2:$B$7), 0)), INDEX($A$2:$A$20, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$20), 0)))

Copy cell C2 to cells below. Note, the ranges don't have to be adjacent.

If your duplicates list starts at, for example, F3. Change C1:$C$1 in the above formulas to F2:$F$2.

Recommended article

Unique distinct values from multiple columns using array formula

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Comments(23) Filed in category: Excel, Unique distinct values

Download excel *.xls file

how-to-extract-a-unique-list-from-two-columns-in-excel-2007.xlsx
(Excel 2007 Workbook *.xlsx)

Recommended article

Extract a unique distinct list sorted from A-Z from range in excel

Inspired from a comment in this article Unique values from multiple columns using array formulas I have now created this […]

Comments(3) Filed in category: Excel, Sorted unique distinct values, Unique distinct values

how-to-extract-a-unique-distinct-list-from-two-columns-in-excel-2003.xls
(Excel 97-2003 Workbook *.xls)

Unique distinct values from two columns, no blanks

how-to-extract-a-unique-list-from-two-columns-in-excel-2007-no-blanks.xlsx
(Excel 2007 Workbook *.xlsx)

Recommended article

Filter a unique distinct list and remove blanks

Question: How do I create a unique distinct list from a list containing several blanks? Answer: Cell range B3:B12 contains […]

Comments(24) Filed in category: Excel, Unique distinct values

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 thirteen articles on the same subject "unique".