Question: I want a unique list to be created from a column where an adjacent

column has cell values between 2 and 8?

Unique values are values without duplicates, within criteria.

Unique distinct values are all values within criteria but duplicates are removed.

### Filter unique values in column A, where numbers in column B are between two criteria

Array formula in cell D5:

=INDEX(List_category, SMALL(IF(FREQUENCY(IF((List_value>=\$E\$1)*(List_value<=\$E\$2), COUNTIF(List_category, "<"&List_category), ""), COUNTIF(List_category, "<"&List_category))=1, ROW(List_category)-MIN(ROW(List_category))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER

### Filter unique distinct values in column A, where numbers in column B are between two criteria

Array formula in cell F5:

=INDEX(List_category, MATCH(0, COUNTIF(\$F\$4:F4, List_category)+(List_value<\$E\$1)+(List_value>\$E\$2), 0)) + CTRL + SHIFT + ENTER

Named ranges
List_value
List_category

### Download excel sample file for this tutorial.Press F9 to recalculate random numbers on this tutorial sheet.

unique-list-with-criteria_1.xls
(Excel 97-2003 Workbook *.xls)

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

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

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE

This blog article is one out of thirteen articles on the same subject "unique".