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

column has cell values between 2 and 8?

Answer:

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

Recommended article

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

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

Recommended article

Unique distinct list sorted alphabetically and based on a condition

The array formula in cell E6 filters values in column C based on value in cell E3, the output is […]

Comments(1) Filed in category: Excel, VLOOKUP and return multiple values

Named ranges
List_value
List_category

Recommended article

Create unique distinct list from column where an adjacent column meets criteria

Question: I want a unique list to be created from a column where an adjacent column has cell values between […]

Comments(2) Filed in category: Excel, Unique distinct values

Download excel sample file for this tutorial

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

Functions in this article:

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