Question: I have a column "A" with a last name.. I have another columb with a date in it "E"... I need to be able to list the names from columb A on a second sheet whos columb e date has past....

Answer:

List-names-whos-date-has-past

Formula in A19:

=INDEX(Name_rng, SMALL(IF(Date_rng<$B$16, ROW(Date_rng)-MIN(ROW(Date_rng))+1, ""), ROWS($B$19:B19))) + CTRL + SHIFT + ENTER

copied down as far as necessary.

Formula in B19:

=INDEX(Date_rng, SMALL(IF(Date_rng<$B$16, ROW(Date_rng)-MIN(ROW(Date_rng))+1, ""), ROWS($B$19:B19))) + CTRL + SHIFT + ENTER

copied down as far as necessary.

Named ranges
Name_rng (A2:A12)
Date_rng (B2:B12)
What is named ranges?

How to implement array formula to your workbook
Change named ranges. Change $B$16 to your "today" date. Change $B$19:B19 to your start cell of your new list.

Download excel example file.
List names whos date has past in excel.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

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

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular range

ROWS(array) returns the number of rows in a reference or an array

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

Related posts:

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

Filter unique rows and sort by date using array formula in excel

Filter duplicate rows and sort by date using array formula in excel

Split first and last names in excel and then sort them alphabetically

Extract dates and adjacent value in a range using a date critera in excel