There are many ways to extract unique items or values from a list or range of cells. In this post, we will consider the formula method to extract unique items from a list.
The general formula for extracting unique values from a list or range of cells is:
It’s an array formula, we need to insert it in the cell by pressing Ctrl+Shift+Enter keys.
Firstly, the formula includes INDEX, MATCH, and COUNTIF functions. We will go through each function in details below:
- INDEX function contains the list or range of cells that contain values with duplicates
- MATCH function searches for an exact match of the value and returns its relative position in an array of
- MATCH function include COUNTIF function and returns 1s where the items of the list are found in active expanding range, and return 0s where the items of the list are not present in active expanding range.
Because we need to find unique items from a list, a search and match 0 as lookup value in an array of 1s and 0s. By default MATCH function returns the relative position of first matched value in an array and based on this relative position INDEX function returns the corresponding value in the list as a unique item.
Secondly, we will go through a specific example for better understanding. Suppose we have a list of names that have duplicate values in range A2:A8 (list). Our goal is to extract unique items from a list. As the result, should be a list of names without duplication.
We will enter syntax in cell C2:
Figure 1. Extracting Unique Values from a List
- “List” is named range for A2:A8
- $C$1:C1 is expanding the range to count the occurrence of items in list or range A2:A8 to return an array of 1s and 0s
- If the formula is entered in cell C2, then expanding range must start from cell C1 as an absolute reference as $C$1 and next part of the range must be a relative reference as C1, like $C$1:C1, so it should expand when the formula is copied down to other rows.
- An array format is a requirement in this formula
The formula returns the relative row position of each unique item in a list using the following syntax:
Furthermore, the MATCH and COUNTIF function purpose are to assign the relative row positions of the cells to be retrieved. COUNTIF function is counting the number of times cells in the unique list appear in the original list, such as:
Applying COUNTIF function in cells must meet criteria
To find relative positions for matching result, MATCH function is a good choice. The function retrieve values by looking for items not present in expanding the range.
Finally, the positions given by the MATCH function are fed into INDEX function as relative_row_position, and INDEX returns the name at that relative position.
Non-array formula method
Additional, another way to extract unique items in Excel is using non-array formula. This is done using the LOOKUP function:
The elements in the formula are almost like the INDEX MATCH formula. Excel LOOKUP function will handle array operation locally.