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.




Excel 2007

Array 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))), "")

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

How to copy array formula

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

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.

Note, the ranges don´t have to be adjacent.

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 *.xls file

(Excel 2007 Workbook *.xlsx)

(Excel 97-2003 Workbook *.xls)

Unique distinct values from two columns, no blanks

(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

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

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