Author: Oscar Cronquist Article last updated on January 12, 2023

Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct list? Merge two lists without duplicates, in other words.

1. Extract a unique distinct list from two columns - Excel 365

Extract a unique distinct list from two columns Excel 365

Excel 365 dynamic array formula in cell F3:

=UNIQUE(TOCOL((B3:B21,D3:D8)))

Explaining formula in cell F3

Step 1 - Combine cell ranges

The comma is a union operator in Excel, it lets you combine multiple cell ranges, however, most Excel functions don't let you do that.

The TOCOL function is one of few functions that let you combine multiple cell ranges, the parentheses are needed in order to not confuse the TOCOL function. Remember the comma is used to separate arguments in Excel functions.

The TOCOL function returns a #VALUE! error if you forget the parentheses, the reason is that the TOCOL function interprets the comma as a delimiter for arguments.

(B3:B21,D3:D8)

Step 2 - Rearrange values to a single column

The TOCOL function rearranges values in 2D cell ranges to a single column.

Function syntax: TOCOL(array, [ignore], [scan_by_col])

TOCOL((B3:B21,D3:D8))

returns

{"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Federer, Roger "; "Davydenko, Nikolay "; "Verdasco, Fernando "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "; "Gonzalez, Fernando "; "Blake, James "; "Nalbandian, David "; "Robredo, Tommy "; "Wawrinka, Stanislas "; "Cilic, Marin "; "Stepanek, Radek "; "Almagro, Nicolas "; "Federer, Roger "; "Ted Hamstedt"; "Murray, Andy "; "Roy Fletcher"; "May Williamsson"; "Del Potro, Juan Martin "}

Why not use the VSTACK function? The TOCOL function has some great features that the VSTACK function is lacking, it lets you ignore blanks and error values. The second argument lets you specify these options.

Step 3 - Extract unique distinct values

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(TOCOL((B3:B21,D3:D8)))

becomes

UNIQUE({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Federer, Roger "; "Davydenko, Nikolay "; "Verdasco, Fernando "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "; "Gonzalez, Fernando "; "Blake, James "; "Nalbandian, David "; "Robredo, Tommy "; "Wawrinka, Stanislas "; "Cilic, Marin "; "Stepanek, Radek "; "Almagro, Nicolas "; "Federer, Roger "; "Ted Hamstedt"; "Murray, Andy "; "Roy Fletcher"; "May Williamsson"; "Del Potro, Juan Martin "})

and returns

{"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "; "Blake, James "; "Nalbandian, David "; "Robredo, Tommy "; "Cilic, Marin "; "Stepanek, Radek "; "Almagro, Nicolas "; "Ted Hamstedt"; "Roy Fletcher"; "May Williamsson"}

2. Extract a unique distinct list from two columns - earlier version 1

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

2.1 Watch a video explaining the formula

2.2 Explaining regular formula in cell F3

This formula consists of two similar parts, one returns values from List1 and the other returns values from List2.

Step 1 - Prevent duplicate values

The COUNTIF function counts values based on a condition, in this case, I am counting values in cells above. This makes sure that duplicates are ignored.

COUNTIF($F$2:F2,$B$3:$B$21)=0

becomes

COUNTIF("Unique distinct list",{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "})=0

becomes

COUNTIF("Unique distinct list",{"Federer, Roger "; "Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay "; "Roddick, Andy ";"Del Potro, Juan Martin "; "Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando "; "Blake, James ";"Nalbandian, David ";"Robredo, Tommy "; "Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "})=0

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}=0

and returns

{TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE}

Step 2 - Divide 1 with array

The LOOKUP function ignores error and if we divide 1 with 0 an error occurs. 1/0 = #DIV/0!

1/(COUNTIF($F$2:F2,$B$3:$B$21)=0)

becomes

1/{TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE}

and returns

{1;1;1;1; 1;1;1;1;1; 1;1;1;1;1;1; 1;1;1;1}

Step 3 - Return value based on array

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

becomes

LOOKUP(2, {1;1;1;1; 1;1;1;1;1; 1;1;1;1;1;1; 1;1;1;1}, $B$3:$B$21)

becomes

LOOKUP(2, {1;1;1;1; 1;1;1;1;1; 1;1;1;1;1;1; 1;1;1;1}, {"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "})

and returns Almagro, Nicolas in cell F3.

Step 4 - Return values from List2

When values run out from List1 formula1 returns errors, the IFERROR function then moves to formula2.

IFERROR(formula1, formula2)

formula2 is just like formula1 except that it returns values from List2.

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

2.3 How to create an array formula

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

Recommended article

Recommended articles

A beginners guide to Excel array formulas
Array formulas allows you to do advanced calculations not possible with regular formulas.

2.4 How to copy formula

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

3. Extract a unique distinct list from two columns - earlier version 2

Extract a unique distinct list from two columns earlier version 2

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

Get excel *.xls file

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

4. Unique distinct values from two columns, no blanks