## Filter unique values from a range using array formula in excel

*Article last updated on June 05, 2013*

**Unique** values in a range are **values occurring only once**. This is what I am going to do in this blog post using array formula.

If you are looking for filtering **unique distinct** values sorted from A to Z, see this blog post: Extract a unique distinct list sorted from A-Z from range in excel

### Filter unique values from a range

### Array formula in B10:

copied down as far as necessary.

**Named ranges**

tbl (B4:E7)

What is named ranges?

### How to implement array formula to your workbook

Change the named range. If your list starts at, for example, F3. Change $B$9:B9 in the above formulas to F2:$F$2.

### Download excel sample file for this tutorial.

Extract-a-unique-list-from-a-range using array formula in excel.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**ROW(**reference**)** returns the rownumber of a reference

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Count cells between a given value

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]

Filter unique values and sort based on adjacent date

Question: How do I filter unique rows and sort by date? Answer: Column A and B are the original list. […]

Extract unique values from two columns

I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]

List all unique events in a month

Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]

Find min and max unique and duplicate numerical values

Question: How do I get the largest and smallest unique and duplicate value? The image below shows you a list […]

Question: I have a list of values, and i want to sum only unique values, how? Answer: If unique values […]

Count unique records between two dates

This blog article is one out of four articles on the same subject. Most common value between two dates in […]

Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel

By coincidence I seem to have created a "unique" formula in this post: Combine data from multiple sheets in excel […]

Extract unique values from a range

Question: How do I extract values only occurring once in a range? Answer: A range (tbl_text) containing text values Array […]

### 2 Responses to “Filter unique values from a range using array formula in excel”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

This formula breaks if you change the size of the "tbl" range to only one or two columns of the same data in the "tbl" range.

How is this fixed so that I could use a range of any size in place of the "tbl" range?

tyler,

the formula works if you use two or more columns. I tried with two columns and it worked.

Use this formula if you are working with one column:

How to extract unique values from a column

Remember, the formulas above filter

uniquevalues. If you are looking forunique distinctvalues, see this post:Extract a unique distinct list sorted from A-Z from range