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.

Download Practice Workbook

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.

Extract unique values based on single criteria in excel

🔑 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.

Extract unique values based on single criteria in excel

🔎 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.

Extract unique values based on multiple criteria

📌 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.

Extract unique values based on multiple criteria

🔎 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.

Extract unique values based on multiple criteria with alternatives

🔎 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.

Extract unique values based on single criteria with array formula

🔎 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.

Extract unique values based on multiple criteria with array formula`

🔎 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.


Related Articles You May Find Interest In

 

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo