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.

Answer:

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.

Download excel example file
unique-list-to-be-created-from-a-column-where-an-adjacent-column-is-in-a-date-range.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

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".

• How to extract a unique distinct list from a column in excel
  
• Extract a unique distinct list from two columns using excel 2007 array formula
  
• Extract a unique distinct list from three columns in excel
• Extract distinct unique sorted year and month list from a date series in excel
  
• Create a unique distinct list from a date range in excel
  
• Unique values from multiple columns using array formulas
  
• Extract a unique distinct list sorted from A-Z from range in excel
  
• Sort a range by occurence using array formula in excel
  
• Filter unique distinct values from two ranges combined in excel 2007
• Create a unique list and sort by occurrances from large to small
  
• Unique list to be created from a column where an adjacent column has text cell values
  
• Create unique list from column where an adjacent column meets criteria
  
• How to create a unique distinct list where other columns meet two criteria

Related posts:

1. How to create a unique distinct list where other columns meet two criteria
2. Create unique distinct list from column where an adjacent column meets criteria
3. Create a unique distinct list and sort by occurrances from large to small
4. Extract a unique distinct list sorted from A-Z from range in excel
5. Extract distinct unique sorted year and month list from a date series in excel
6. Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
7. Extract a unique distinct list from two columns using excel 2007 array formula
8. Count unique distinct records in a date range and a numeric range in excel
9. Extract a unique distinct list from three columns in excel
10. Unique distinct list from a column sorted A to Z using array formula in excel