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

How to enter an array formula

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 […]

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 […]

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. […]

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 […]

5 easy ways to extract unique distinct values

Update: 30 Aug, 2017h You have quite a few options to choose from if you are looking for a way […]

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

Recommended article

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

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

Recommended article

Extract a unique distinct list by matching items that meet a criterion

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]

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)

Recommended article

Create a unique distinct alphabetically sorted list extracted from a column

The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]

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