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

Answer:

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))), "") + CTRL + SHIFT + ENTER copied down to D22.

Named ranges
List1 (A2:A20)
List2 (B2:B7)
List3 (C2:C12)
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 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-excel1.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 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 unique distinct list from two columns using excel 2007 array formula
2. How to create a unique distinct list where other columns meet two criteria
3. How to extract a unique distinct list of a column in excel
4. Extract a unique distinct list sorted from A-Z from range in excel
5. Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
6. Extract distinct unique sorted year and month list from a date series in excel
7. Unique distinct values from multiple columns using array formula
8. Extract a list of duplicates from three columns combined using array formula in excel
9. Extract a list of duplicates from two columns combined using array formula in excel
10. Unique distinct list sorted based on occurrance in a column in excel