Article updated on February 05, 2018

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.

Update! 2017-09-01, smaller regular formula in cell F3:

=IFERROR(LOOKUP(2, 1/(COUNTIF(\$F\$2:F2,\$B\$3:\$B\$21)=0), \$B\$3:\$B\$21), LOOKUP(2, 1/(COUNTIF(\$F\$2:F2, \$D\$3:\$D\$8)=0), \$D\$3:\$D\$8))

Array formula in F3:

=IFERROR(IFERROR(INDEX(\$B\$3:\$B\$21, MATCH(0, COUNTIF(\$C\$1:C1, \$B\$3:\$B\$21), 0)), INDEX(\$D\$3:\$D\$8, MATCH(0, COUNTIF(\$C\$1:C1, \$D\$3:\$D\$8), 0))), "")

#### Watch a video explaining the 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

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

Extract a unique distinct list from three columns

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

How to create an array formula

1. Select cell C2
2. Click in formula bar
3. Paste array formula to formula bar
4. Press and hold Ctrl+ Shift
5. Press Enter
6. Release all keys

Recommended article

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

How to copy array formula

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

Earlier versions of Excel 2007

Array formula in cell C2:

=IF(ISERROR(INDEX(\$A\$2:\$A\$20, MATCH(0, COUNTIF(\$C\$1:C1, \$A\$2:\$A\$20), 0))), INDEX(\$B\$2:\$B\$7, MATCH(0, COUNTIF(\$C\$1:C1, \$B\$2:\$B\$7), 0)), INDEX(\$A\$2:\$A\$20, MATCH(0, COUNTIF(\$C\$1:C1, \$A\$2:\$A\$20), 0)))

Recommended article

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

how-to-extract-a-unique-list-from-two-columns-in-excel-2007.xlsx
(Excel 2007 Workbook *.xlsx)

Recommended article

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

how-to-extract-a-unique-distinct-list-from-two-columns-in-excel-2003.xls
(Excel 97-2003 Workbook *.xls)

### Unique distinct values from two columns, no blanks

how-to-extract-a-unique-list-from-two-columns-in-excel-2007-no-blanks.xlsx
(Excel 2007 Workbook *.xlsx)

Recommended article

Filter a unique distinct list and remove blanks

Question: How do I create a unique distinct list from a list containing several blanks? Answer: Cell range B3:B12 contains several […]