How to Extract Unique Values Based on Criteria in Excel

Get FREE Advanced Excel Exercises with Solutions!

Extracting unique values based on single or different criteria is very common in offices & businesses. Microsoft Excel has also added a few useful & handy methods to extract unique values from a large dataset. In this article, I’ll try to illustrate how to extract unique values based on criteria in Excel with 2 effective methods.


Watch Video – Extract Unique Values Based on Criteria in Excel



How to Extract Unique Values Based on Criteria in Excel (2 Effective Methods)

For illustration, here is a sample dataset. Here, we have a chart of 5 Computer Shops. They have stocked new desktops and notebooks in the months of June and July in their shops.

2 Effective Methods to Extract Unique Values Based on Criteria in Excel


Now, we will try to find unique products from this dataset based on several criteria.

1. Combine Excel UNIQUE & FILTER Functions to Extract Unique Values

In this first method, let us use the UNIQUE function and the FILTER function in Excel to extract unique values. Here, we will use these functions for both single and multiple criteria. So without further delay, let’s jump into the methods.

1.1. Single Criteria

Here, we want to know which shops have stocked only notebooks, or only desktops, or both for 2 successive months in a year.

  • Firstly, select cell E5 & type this formula
=UNIQUE(FILTER(C5:C14,D5:D14=E4))

Combine Excel UNIQUE & FILTER Functions to Extract Unique Values

  • Secondly, press Enter & you will see the names of 4 computer shops that have stocked notebooks for over 2 months.

In this formula, for column E, the FILTER function extracts all the names of the shops from column C that have stocked the notebook for only over 2 months. So, similar names can appear multiple times. Then, the UNIQUE function will show all the names only once.
  • Next, type this formula cell F5 to find out who has stocked desktops among those 5 shops.
=UNIQUE(FILTER(C5:C14,D5:D14=F4))
  • Then, press Enter & you will get the names of 3 shops that have stocked desktops over those months.

  • You can also compare these two findings & you’ll notice that only Computer Sphere & EMACIMAC have stocked both types of devices.

1.2. 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 over those 2 months.

Combine Excel UNIQUE & FILTER Functions to Extract Unique Values

  • First, select cell G12 where we want to see the names of shops that have stocked HP notebooks.
  • Then, type this formula in that cell.
=UNIQUE(FILTER(C5:C14, (D5:D14=D5) * (E5:E14=E6)))
  • Afterward, press Enter.
  • Finally, you’ll notice only 1 shop has stocked notebooks of HP over those 2 months.

Here, the FILTER function evaluates two criteria- one for the Device Category & another one for the 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. 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.

  • First, select cell G11.
  • Then, type this formula.
=UNIQUE(FILTER(C5:C14, (E5:E14=E6) + (E5:E14=E7)))
  • Following, press Enter.
  • Finally, you’ll see the names of 4 shops that have stocked devices of either HP or ASUS.

Combine Excel UNIQUE & FILTER Functions to Extract Unique Values

In this formula, the FILTER function will assess two criteria separately & show combined results or shop names from both categories. Then, the UNIQUE function will then show these names only once.

2. Apply Array Formula to Pull Out Unique Values Based on Criteria in Excel

In this second method, we will use 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 for both single and multiple criteria.

2.1. Single Criteria

Now, let’s go to steps on how we can bring out the names of the shops that have stocked notebooks or desktops over 2 months with the help of array formulas.

  • First, in cell E5 under the Notebook title, type this formula.
=IFERROR(INDEX($C$5:$C$14, MATCH(0, IF($E$4=$D$5:$D$14, COUNTIF($E$4:$E4, $C$5:$C$14), ""), 0)),"")

Apply Array Formula to Pull Out Unique Values Based on Criteria in Excel

  • Then, press Enter.
  • Afterward, use the Fill Handle command to fill down the whole column & you’ll find the names of 4 computer shops that have stocked notebooks.

In this complex formula,
  • Initially, the COUNTIF function ensures column E under the 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, 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, the MATCH function searches for 0 only in the array found through the IF function previously.
  • 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 error messages & replace them with empty strings.

Similarly, apply the array formula in cell F5 to find out the shop names that have Desktop in stock.


2.2. Multiple Criteria

If we have to deal with two or more criteria to get unique values in Excel then here’s the solution for you. We are now going to find out the shops that have stocked notebooks of the HP brand only for over 2 months.

  • First, select cell G12.
  • Then, type this formula.
=IFERROR(INDEX($C$5:$C$14,MATCH(0,COUNTIF(G$11:$G11,$C$5:$C$14)+IF($D$5:$D$14<>$D$5,1,0)+IF($E$5:$E$14<>$E$6,1,0),0)),"")
  • Following, press Enter.
  • Lastly, use Fill Handle to fill down the column until you’re shown blank cells at the bottom & you’re done.

Apply Array Formula to Pull Out Unique Values Based on Criteria in Excel

  • Here, the IF function is used twice. At first, searches for the Notebook category in column D & returns the results as 0 in the array.
  • Similarly, searches for the HP brand in column E & return the results as 0 in another array.
  • Then, the COUNTIF function here counts all the company names & will return the values as 0 in an array for all names found in column C under the Company title.
  • Now, the MATCH function searches for the positions of 0 found as the resultant sum values alongside the last 3 arrays.
  • Next, the INDEX function stores all this 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 error messages & display the shop names only.

Download Practice Workbook

You can download our Excel Workbook that we’ve used to prepare this article.


Conclusion

I hope the methods mentioned above on how to extract unique values based on criteria in Excel will now help you to apply them in your Excel works and meet your requirements in analytical functions and 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.


<< Go Back to Unique Values | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo