Quick, clean, and to the point

Extract unique items from a list

Excel formula: Extract unique items from a list
Generic formula 

To extract only unique values from a list or column, you can use an array formula based on INDEX, MATCH, and COUNTIF. In the example shown, the formula in D5 is:


where "list" is the named range B5:B11.

Note: this is an array formula and must be entered using control + shift + enter.

How this formula works

The core of this formula is a basic lookup with INDEX:


In other words, give INDEX the list and a row number, and INDEX will retrieve a value to add to the unique list.

The hard work is figuring out the ROW number to give INDEX, so that we get unique values only. This is done with MATCH and COUNTIF, and the main trick is here:


Here, COUNTIF counts how many times items already in the unique list appear in the master list, using an expanding reference for the range, $D$4:D4.

An expanding reference is absolute on one side, relative on the other. In this case, when the formula is copied down, the reference will expand to include more rows in the unique list.

Note the reference starts in D4, one row above the first unique entry, in the unique list. This is intentional — we want to count items  *already* in the unique list, and we can't include the current cell without creating a circular reference. So, we start on the row above.

Important: be sure the heading for the unique list does not appear in the master list.

For the criteria in COUNTIF, we are using the master list itself. When given multiple criteria, COUNTIF will return multiple results in an array. At each new row, we have a different array like this:

{0;0;0;0;0;0;0} // row 5
{1;0;0;0;1;0;0} // row 6
{1;1;0;0;1;0;1} // row 7
{1;1;1;1;1;0;1} // row 8

Note: COUNTIF handles multiple criteria with an "OR" relationship (i.e. COUNTIF (range, {"red","blue", "green"}) counts red, blue, or green.

Now we have the arrays we need to find positions (row numbers). For this, we use MATCH, set up for exact match, to find zero values. If we put the arrays created by COUNTIF above into MATCH, here is what we get:

MATCH(0,{0;0;0;0;0;0;0},0) // 1 (Joe)
MATCH(0,{1;0;0;0;1;0;0},0) // 2 (Bob)
MATCH(0,{1;1;0;0;1;0;1},0) // 3 (Sue)
MATCH(0,{1;1;1;1;1;0;1},0) // 6 (Aya)

MATCH locates items by looking for a count of zero (i.e. looking for items that do not yet appear in the unique list). This works, because MATCH always returns the first match when there are duplicates.

Finally, the positions are fed into INDEX as row numbers, and INDEX returns the name at that position.

Non array version with LOOKUP

You can build a non-array formula to extract unique items using the flexible LOOKUP function:


The formula construction is similar to the INDEX MATCH formula above, but LOOKUP can handle the array operation natively.

  • COUNTIF returns counts of each value from "list" in the expanding range $D$4:D4
  • Comparing to zero creates an array of TRUE and FALSE values
  • The number 1 is divided by the array, creating an array of 1s and  #DIV/0 errors
  • This array becomes the the lookup_vector inside LOOKUP
  • The lookup value of 2 is larger than any values in the lookup_vector
  • LOOKUP will match the last non-error value in the lookup array
  • LOOKUP  returns the corresponding value in result_vector, the named range "list"

Thanks, Oscar!

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.