Article updated on February 18, 2018

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.

### Extract a unique distinct list from three columns

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 and ignore 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

Inspired from a comment in this article Unique values from multiple columns using array formulas I have now created this […]

Extract unique distinct values from a multi-column cell range

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

You have quite a few options to choose from if you are looking for a way to create a unique […]

### Extract a unique distinct list from three columns 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 and sum amounts based on a condition

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.

Change named ranges. If your duplicates list starts at, for example, F3. Change D1:\$D\$1 in the above formulas to F2:\$F\$2.

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

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