Question: I have a large list of dates and other adjacent values. I want to create a distinct list from the large list with a criteria, only values within a specific date interval.

The date range is specified in cell G1 and G2.

Here is the formula:

=INDEX(\$A\$2:\$A\$17, SMALL(IF(ISERROR(IF(MATCH(IF((B2:B17<\$J\$2)*(B2:B17>\$J\$1), List, ""), IF((B2:B17<\$J\$2)*(B2:B17>\$J\$1), List, 0), 0)=ROW(List)-1, ROW(List)-1, "")), "", IF(MATCH(IF((B2:B17<\$J\$2)*(B2:B17>\$J\$1), List, ""), IF((B2:B17<\$J\$2)*(B2:B17>\$J\$1), List, 0), 0)=ROW(List)-1, ROW(List)-1, "")), ROW()-1)) + CTRL + SHIFT + ENTER

To also remove #num errors, the formula grows significantly:

=IF(ISERROR(SMALL(IF(ISERROR(IF(MATCH(IF((B2:B17<\$J\$2)*(B2:B17>\$J\$1), List, ""), IF((B2:B17<\$J\$2)*(B2:B17>\$J\$1), List, 0), 0)=ROW(List)-1, ROW(List)-1, "")), "", IF(MATCH(IF((B2:B17<\$J\$2)*(B2:B17>\$J\$1), List, ""), IF((B2:B17<\$J\$2)*(B2:B17>\$J\$1), List, 0), 0)=ROW(List)-1, ROW(List)-1, "")), ROW()-1)), "", INDEX(\$A\$2:\$A\$17, SMALL(IF(ISERROR(IF(MATCH(IF((B2:B17<\$J\$2)*(B2:B17>\$J\$1), List, ""), IF((B2:B17<\$J\$2)*(B2:B17>\$J\$1), List, 0), 0)=ROW(List)-1, ROW(List)-1, "")), "", IF(MATCH(IF((B2:B17<\$J\$2)*(B2:B17>\$J\$1), List, ""), IF((B2:B17<\$J\$2)*(B2:B17>\$J\$1), List, 0), 0)=ROW(List)-1, ROW(List)-1, "")), ROW()-1))) + CTRL + SHIFT + ENTER

Named ranges
List
(A2:A17)
What is named ranges?

Try changing dates (yellow cells) and see different cells highlighted.

(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

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

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

ISTEXT(value)
Checks whether a value is text, and returns TRUE or FALSE

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

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

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

### Related posts:

Create a unique distinct text list from a range containing both numerical and text values in excel

Count unique distinct records in a date range and a numeric range in excel

Create unique distinct year and months from a long date listing in excel

Count unique distinct values using date criteria in a range in excel

Extract a unique distinct list sorted from A-Z from range in excel