Article updated on December 20, 2010

In a previous article I came up with a solution on how to extract distinct unique year-month list from a date series. In this article I will use this unique distinct list in a drop down list, making it easy to visualize alla data in given month and year.

Here is a picture of a distinct unique year-month list (Column E), extracted from a date series (Column A).


Create a drop down list
See drop down list on cell A19 on picture below

  1. Click Data tab
  2. Click Data validation button
  3. Click "Data validation..."
  4. Select List in the "Allow:" window. See picture below.
  5. Type =$E$2:$E$15 in the "Source:" window
  6. Click OK!


Formula in A22:

=INDEX($A$2:$C$15, SMALL(IF($A$19=TEXT($A$2:$A$15, "YYYY-mmm"), ROW($A$2:$A$15)-MIN(ROW($A$2:$A$15))+1), ROW(A1)), COLUMN(A1)) + CTRL+ SHIFT + ENTER

copied to the right and then down as far as needed.

Download example workbook

Download excel sample file for this tutorial.
(Excel 97-2003 Workbook *.xls)

Functions in this article:

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

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

TEXT(value, format_text)
Converts a value to text in a specific number format