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.

Answer:

Excel 2007

Formula in C2:

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($C$1:C1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($C$1:C1, List2), 0))), "") + CTRL + SHIFT + ENTER copied down to C20.

Earlier versions of Excel

Formula in C2:

=IF(ISERROR(INDEX(List1, MATCH(0, COUNTIF($C$1:C1, List1), 0))), INDEX(List2, MATCH(0, COUNTIF($C$1:C1, List2), 0)), INDEX(List1, MATCH(0, COUNTIF($C$1:C1, List1), 0))) + CTRL + SHIFT + ENTER copied down to C20.

The ranges don´t have to be adjacent, these formulas work fine anyway.

Named ranges
List1 (A2:A20)
List2 (B2:B7)
What is named ranges?

How to customize the formula to your excel spreadsheet
Change named ranges. If your duplicates list starts at, for example, F3. Change C1:$C$1 in the above formulas to F2:$F$2.

Download excel sample file for this tutorial.

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

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

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".

• How to extract a unique distinct list from a column in excel
  
• Extract a unique distinct list from two columns using excel 2007 array formula
  
• Extract a unique distinct list from three columns in excel
• Extract distinct unique sorted year and month list from a date series in excel
  
• Create a unique distinct list from a date range in excel
  
• Unique values from multiple columns using array formulas
  
• Extract a unique distinct list sorted from A-Z from range in excel
  
• Sort a range by occurence using array formula in excel
  
• Filter unique distinct values from two ranges combined in excel 2007
• Create a unique list and sort by occurrances from large to small
  
• Unique list to be created from a column where an adjacent column has text cell values
  
• Create unique list from column where an adjacent column meets criteria
  
• How to create a unique distinct list where other columns meet two criteria

Related posts:

1. Extract a list of duplicates from two columns combined using array formula in excel
2. Extract a unique distinct list from three columns in excel
3. Extract a list of duplicates from three columns combined using array formula in excel
4. Extract largest values from two columns using array formula in excel
5. Comparing two columns and sum unique values using array formula in excel
6. Create unique list from two columns
7. Unique distinct values from multiple columns using array formula
8. Filter duplicates from two columns combined and sort from A to Z using array formula in excel
9. Unique distinct list from a column sorted A to Z using array formula in excel
10. Extract a list of duplicates from a column using array formula in excel