# How to Extract Unique Values Based on Criteria in Excel

Extracting unique values based on single or different criteria is very common in office & business. Microsoft Excel has also added a few useful & handy methods to extract the unique values from a large dataset. In this article, Iâ€™ll try to illustrate how you can extract unique values in Excel under a number of criteria within a few minutes.

You can download our Excel Workbook that weâ€™ve used to prepare this article. You can also do some exercises yourself there as weâ€™ve made an extra dataset to input values & formulas.

## Methods to Extract Unique Values Based on Criteria in Excel

### 1. Using UNIQUE And FILTER Functions Together to Extract Unique Values

#### 1.1 Combining UNIQUE & FILTER Functions to Pull Out Unique Values Based on Single Criteria

We have a chart of 5 Computer Shops. They stock new desktops or notebooks every month in their shops. We want to know which shops have stocked only notebooks, or only desktops, or both for 3 successive months in a year.

ðŸ”‘ Base Formula:

=UNIQUE(FILTER(array, include, [if empty])

ðŸ“Œ Steps:

âž¤ Select Cell E5 & type-

`=UNIQUE(FILTER(C5:C19,D5:D19=E4))`

âž¤ Press Enter & youâ€™ll see the names of 4 computer shops who have stocked notebooks for over 3 months. OMICSIRIX is missing in the list as they havenâ€™t stocked any notebook yet over that period.

âž¤ Again select Cell F5 as weâ€™ll now find who have stocked desktops among those 5 shops.

âž¤ Type-

`=UNIQUE(FILTER(C5:C19,D5:D19=F4))`

âž¤ Press Enter & youâ€™ll find the names of 3 shops that have stocked desktops over those months. So, here Qubitmbit & Computism havenâ€™t stocked the desktop.

You can also compare between these two findings & youâ€™ll notice that only Computer Sphere & EMACIMAC have stocked both types of devices.

ðŸ”Ž How Does This Formula Work?

âž¤ For Column E, the FILTER function extracts all the names of the shops from Column C who have stocked the notebook for only over 3 months. So, similar names can appear multiple times.

âž¤ UNIQUE function will then show all the names only once.

Note: If you use Microsoft Office Excel 2019 or Office 365 then youâ€™ll be able to use the UNIQUE function directly which we need now to extract unique values directly based on criteria. But if youâ€™re using MSOffice 2016 or older versions, then we have a solution for you too in the 2nd method where weâ€™ll apply array formulas to extract unique values.

#### 1.2 Incorporating UNIQUE & FILTER Functions to Extract Unique Values Based on Multiple Criteria

Now we want to add one more criterion to the previous dataset. The computer shops have brought notebooks & desktops of 3 different brands- Lenovo, HP & Asus. And weâ€™re going to find out which shops have stocked HP notebooks of HP over those 3 months.

ðŸ“Œ Steps:

âž¤ Select Cell G12 where we want to see the names of shops that have stocked HP notebooks.

âž¤ Type-

`=UNIQUE(FILTER(C5:C19, (D5:D19=D5) * (E5:E19=E6)))`

âž¤ Press Enter & youâ€™ll notice only 2 shops- Computer Sphere & Computism have stocked notebooks of HP over those 3 months.

ðŸ”Ž How Does This Formula Work?

âž¤ FILTER function here evaluates two criteria- one for device category & another one for brand. And we have to add these two criteria by putting an Asterisk(*) between them in the formula.

âž¤ Like before, the UNIQUE function will show these shop names only once.

#### 1.3 Fusing UNIQUE & FILTER Functions to Draw Out Unique Values Based On Multiple Criteria with Alternatives

Now weâ€™re going to deal with another condition where we want to find out which shops have stocked devices of at least one from HP or ASUS.

ðŸ“Œ Steps:

âž¤ In Cell G11, type-

`=UNIQUE(FILTER(C5:C19, (E5:E19=E6) + (E5:E19=E7)))`

âž¤ Press Enter & youâ€™ll be shown the names of 4 shops that have stocked devices of either HP or ASUS.

ðŸ”Ž How Does This Formula Work?

âž¤ In this formula, the FILTER function will assess two criteria separately & show combined results or shop names from both categories.

âž¤ UNIQUE function will then show these names only once.

### 2. Applying Array Formula to Pull Out Unique Values in Excel

#### 2.1 Inserting Array Formula to Extract Unique Values Based on Single Criteria

Now, hereâ€™s an array formula that you can use in any version of Microsoft Excel. Though you may find it a bit complex, Iâ€™ll try to explain later how this formula works. Now, letâ€™s go to steps on how we can bring out the names of the shops that have stocked notebooks or desktops over 3 months with the help of array formulas.

ðŸ“Œ Steps:

âž¤ As weâ€™re going to find out the names of the shops who have stocked notebooks over 3 months, so in Cell E5 under Notebook title, type-

`=IFERROR(INDEX(\$C\$5:\$C\$19, MATCH(0, IF(\$E\$4=\$D\$5:\$D\$19, COUNTIF(\$E\$4:\$E4, \$C\$5:\$C\$19), ""), 0)),"")`

âž¤ Press Enter, then use Fill Handle to fill down the whole column & youâ€™ll find the names of 4 computer shops that have stocked notebooks.

ðŸ”Ž How Does This Formula Work?

âž¤ In this complex formula, initially COUNTIF function ensures Column E under Notebook title that all the company names will appear here & thereby makes an array with a common 0 for all company names with multiple appearances.

âž¤ Outside this COUNTIF function, the IF function now finds out which shops have stocked notebooks only. So, it removes 0 from the names of the shops that have not stocked notebooks.

âž¤ After that, MATCH function searches for 0 only in the array found through IF function previously & where 0 is not found, those cells will be displayed Value Error.

âž¤ Now, the INDEX function stores all the cells in that array as a reference & shows the names of the shops only once those have appeared multiple times.

âž¤ Finally, the IFERROR function will remove all the Value Error messages & replace them with empty strings.

Thus, weâ€™ll finally get the names of 4 computer shops that have stocked notebooks in their shops. By following a similar process, we can find the names of the shops in Column F that have brought in desktops over those 3 months.

#### 2.2 Using Array Formula to Bring Out Unique Values Based on Multiple Criteria

If we have to deal with two or more criteria while extracting unique values in Excel then hereâ€™s the solution for you as weâ€™re now going to find out the shops that have stocked notebooks of HP brand only for over 3 months.

ðŸ“Œ Steps:

âž¤ In Cell G12, we have to type for our dataset-

`=IFERROR(INDEX(\$C\$5:\$C\$19,MATCH(0,COUNTIF(G11:\$G\$11,\$C\$5:\$C\$19)+IF(\$D\$5:\$D\$19<>\$D\$5,1,0)+IF(\$E\$5:\$E\$19<>\$E\$6,1,0),0)),"")`

âž¤ Press Enter, then use Fill Handle to fill down the column until youâ€™re shown blank cells at the bottom & youâ€™re done.

`

ðŸ”Ž How Does This Formula Work?

âž¤ IF function is used here twice. At first, searches for Notebook category in Column D & returns the results as 0â€™s in the array.

âž¤ Similarly, another IF function searches for HP brand in Column E & returns the results as 0â€™s in another array.

âž¤ COUNTIF function here counts all the company names & will return the values as 0â€™s in an array for all names found in Column C under Company title.

âž¤ MATCH function now searches for the positions of 0 found as the resultant sum values alongside from the last 3 arrays & if not found, the resulting cells will show Value Error messages.

âž¤ INDEX function stores all these data as a reference array & shows the related names of the shops by the row positions of the resultant value 0 found in the preceding step.

âž¤ And last of all, the IFERROR function will remove all the Value Error messages & display the shop names only.

## Concluding Words

I hope these methods mentioned above with visual illustrations will now help you to apply in your Excel works & meet your requirements in analytical functions & data entries. You can also suggest to me in the comment box if you think Iâ€™ve missed a method that I shouldâ€™ve mentioned in my article. Iâ€™ll update it soon. Or you can have a look at our other interesting & informative articles based on Excel functions on this website.