Extract unique items from a list

An excel formula to extract unique items from a list

Related Functions

 Extract unique items from a list

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:


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. When the formula is copied down, the reference will expand to include more rows in the unique list.

Note that 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. With given multiple criteria, COUNTIF will return an array of results (a different array at each row) that look 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.

0 votes. 0 / 5