Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent or the exact same length.

Answer:

Extract a unique distinct list from three columns

unique-distinct-values-in-three-columns1

Array formula in D2:

=IFERROR(IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($D$1:D1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($D$1:D1, List2), 0))), INDEX(List3, MATCH(0, COUNTIF($D$1:D1, List3), 0))), "")

Extract a unique distinct list from three columns with possible blanks

extract a unique distinct list from three column with possible blanks

Array formula in D2:

=IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$20)+($A$2:$A$20=""), 0)), INDEX($B$2:$B$7, MATCH(0, COUNTIF($D$1:D1, $B$2:$B$7)+($B$2:$B$7=""), 0))), INDEX($C$2:$C$12, MATCH(0, COUNTIF($D$1:D1, $C$2:$C$12)+($C$2:$C$12=""), 0))), "")

How to enter an array formula

  1. Select cell D2
  2. Click in formula bar
  3. Paste array formula to formula bar
  4. Press and hold CTRL + SHIFT
  5. Press ENTER

How to copy array formula

Copy cell D2 and paste it down as far as needed.

How to customize the array formula to your excel spreadsheet

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-unique-list-from-three-columns-in-excelv2.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