Create a Unique List in Excel Based on Criteria (9 Methods)

In Microsoft Excel, several methods are available to create a unique list under multiple criteria. The unique list is generally prepared to remove duplicates from a table. In this article, you’ll learn how to use different methods to generate a unique list based on several conditions.

How to Create a Unique List in Excel Based on Criteria: 9 Methods

1. Create a List of Unique Rows with Multiple Columns Criteria

In the following picture, the table on the left is lying with several random sports names, the participants and their ages. Another table on the right is the output table where we’ll extract data based on the unique values only. For example, we want to know the names of the unique participants only along with their ages.

We’ll use the UNIQUE function here which returns the unique values from a range or array. The generic formula of this function is:

=UNIQUE(array, [by_col], [exactly_once])

Although the UNIQUE function is only available in Microsoft 365, we’ll also show the alternative methods with the combinations of other conventional functions in the last 3 sections before jumping into the concluding words.

So, the required function in cell F5 will be:

After pressing Enter, the function will return an array with the unique names along with the corresponding ages of the participants.

Read More: How to Generate List Based on Criteria in Excel

2. Get a List of Unique Values Sorted in Alphabetical Order

We can also get the unique values sorted in alphabetical order by using the SORT function. By denoting the sort order with ascending (A to Z) or descending (Z to A) criteria, we can rearrange the participant names based on their initials.

In the output cell E5, the required formula with the SORT and UNIQUE functions will be:

After pressing Enter, the function will return the array sorted in alphabetical order by A to Z.

Read More: How to Make Alphabetical List in Excel

3. Make a List of Unique Values Concatenated into One Cell

Let’s assume, we want to extract unique data from two columns and then join the values alongside to return concatenated values in a single column. In the following picture, the unique names and the corresponding ages will be shown together in a column by using a specific delimiter- Comma (,).  And We’ll use Ampersand (&) here to concatenate the values alongside two different columns.

The required formula in the output Cell F5 will be:

The formula will return an array of unique rows in a single column.

Read More: How to Make a Comma Separated List in Excel

4. Create a List of Unique Values with Criteria (UNIQUE-FILTER Formula)

i. Identify Unique Values Based on Multiple AND Criteria in Excel

In this section, we’ll add a few criteria and extract unique data based on those conditions. For example, we want to know the unique names of the participants who have participated in swimming only and are below 25 years old. So, we have to combine UNIQUE and FILTER functions here to filter data with the given criteria.

The necessary formula in the output cell F5 will be:

After pressing Enter, the formula will return an array with the unique names and the corresponding ages based on the selected criteria.

ii. Search Unique Values Based on Multiple OR Criteria

Let’s assume, now we want to know the names of the unique participants who took part in both outdoor sports- swimming and cycling. As we’re assigning the criteria from only one column, we have to simply use numerical addition to join two conditions in the FILTER function.

The required formula in cell F5 will be now:

Press Enter and you’ll get the extracted unique data right away as shown in the following screenshot.

iii. Get a List of Unique Values Ignoring Blank Cells

Our dataset may contain several blank cells or rows. So, if we want to skip those blank rows and extract unique data from the table we have to use a comparison operator: Not Equal To (<>) in the formula.

The required formula in the output cell F5 will be:

After pressing Enter, the formula will return the following array with only non-blank unique rows in the output table.

Read More: How to Make a To Do List in Excel

5. Find a List of Unique Values in Specified Columns in Excel

Assuming that we want to extract unique data from a number of specific columns only. It’s not possible to select those distinct columns with a mouse cursor and input it as the argument for the UNIQUE function. So, we have to combine the CHOOSE function here with the UNIQUE function as the CHOOSE function will let you select any number of specified columns or the ranges of cells based on the index numbers from a list of values.

From our data table, we’re going to extract the names of the unique participants and show the outputs from the Name (Column B) and Sports Name (Column D) columns only.

The required formula in Cell F5 will be:

After pressing Enter, the formula will return the unique names and the names of the corresponding sports from two distinct columns as shown in the picture below.

Inside the CHOOSE function, the index numbers are 1 and 3 which implies that the 1st and 3rd ranges of cells have to be selected from the list of values. The UNIQUE function then considers the specified columns only and returns the unique data from those columns in an array.

Read More: How to Make a Numbered List in Excel

6. Use of IFERROR Function While Creating a Unique List

We can also use the IFERROR function behind the UNIQUE function to display a customized message if any error is found in the return value. Let’s assume, we want to know the names of the unique participants who are under 21. As there is no instance of any participant under 21, the formula should return a #N/A error. But instead of showing this error, we’ll rather use the IFERROR function to display a customized message- “Not Found”.

So, the required formula in Cell F5 will be:

After pressing Enter, the formula will return the specified message as shown in the following screenshot.

7. Extract a Unique List Based on Criteria (INDEX-MATCH Formula)

Now we’ll get rid of the UNIQUE function and apply the combination of INDEX and MATCH functions. The INDEX function returns a value or reference of the cell at the intersection of the particular row and column. And the MATCH function returns the position of an item that matches the specified value in a specified order. Assuming that we want to know the unique names of the participants who took part in swimming only.

📌 Step 1:

➤ Select the output Cell F5 and type:

➤ Press Enter.

You’ll get the first unique name as the return value.

📌 Step 2:

➤ Use the Fill Handle to drag the cell to the right.

And you’ll find the age of the participant too.

📌 Step 3:

➤ From Cell G5, fill down the column until a #N/A value appears.

So, this is how you can extract unique data based on a given condition with the INDEX-MATCH formula.

🔎 How Does the Formula Work?

• COUNTIF($F$4:$F4, $B$5:$B$13): The COUNTIF function here has been used to store and count all the cells available in the range of B5:B13. And the function returns as:

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

• IF($F$12=$D$5:$D$13, COUNTIF($F$4:$F4, $B$5:$B$13), “”): The IF function searches for the given criteria in the cells and returns as:

{0;””;0;””;””;0;””;””;””}

• The MATCH function returns the row number of the cell found in the previous step.
• Finally, the INDEX function extracts data based on those row numbers.

8. Prepare a Unique List in Excel Based on Multiple Criteria

In this section, we’ll find out how the INDEX-MATCH formula works out when multiple conditions are applied. For example, we want to know the unique names of the participants who have participated in swimming and are less than 25 years old.

The required formula in the output cell F5 will be:

After pressing Enter and auto-filling the new cells in the output column, we’ll get the unique names as shown in the picture below.

In this formula, we have assigned the criteria with two IF functions and the COUNTIF function will consider those criteria while extracting the array of output cells. As described in the previous method, the INDEX-MATCH formula will return the output based on that array. And the IFERROR function here has been used to return a customized message if any error is found.

9. Make Multiple Unique Lists Along Rows and Columns with Criteria

In our last method, we’ll use an Excel table to extract multiple unique lists of data. In the following picture, the table at the bottom will show the unique names of the participants based on the sports types.

We’ve named the table or the range of cells (B5:C13) with Sports. The columns have headers with the names- SportsName and Name. You must keep in mind that the headers in the table cannot occupy any space.

📌 Step 1:

➤ The required formula in the first output cell B16 will be:

After pressing Enter, the first output value will return at once.

📌 Step 2:

➤ Use Fill Handle to fill down the column until a blank cell appears.

📌 Step 3:

➤ Now copy the range of cells from the outputs (B16:B20) in the first column.

➤ In Cell C16, paste the values with the Formulas (F) option.

You’ll get the second column with the unique names of the participants who took part in cycling only.

📌 Step 4:

➤ Similarly, paste the copied values as formulas again in cells D16 and E16.

And you’ll get the other unique names of the participants based on the sports types right away. But here you must remember that you cannot simply use Fill Handle to autofill the cells rightward from the first return values in Column B. Using the Autofill option will result in manipulated data and you won’t be able to extract the original output.

🔎 How Does the Formula Work?

• IF(Sports[SportsName]=B$15, ROW(Sports)-4): This part of the function searches for the given condition specified by the header in Cell B15 and the function returns as:

{1;FALSE;FALSE;4;FALSE;FALSE;7;8;FALSE}

• SMALL(IF(Sports[SportsName]=B$15, ROW(Sports)-4), ROW(1:1)): The SMALL function extracts the smallest number from the previous output and for Cell C16, it’s ‘1’.
• The INDEX function pulls out the name based on the row number specified by the SMALL function.
• The IFERROR function has been used to show blank cells if any error output is found.
• In this combined formula, the number ‘4’ in the portion- “ROW(Sports)-4” is the row number of the header in the primary data table.

Read More: How to Create List from Range in Excel

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Concluding Words

I hope all the methods illustrated above will now help you to apply them in your Excel spreadsheets more effectively while creating a list of unique values from a data table. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

Related Articles

• How to Make a Bulleted List in Excel
• How to Make a List within a Cell in Excel
• How to Make a Price List in Excel
• How to Create a Contact List in Excel

<< Go Back to Make List in Excel | Excel Drop-Down List | Data Validation in Excel | Learn Excel