I have a column of dates in the format mm/dd/yyyy. I want to find the first
occurence of the year 2005 in that column.
LOOKUP(2005,YEAR($A$2:$A$114),$A$2:$A$114) gives me the last occurence. How
can I find the first occurence? Any ideas?

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX($A$2:$A$114,MATCH(TRUE,YEAR($A$2:$A$114)=20 05,0))

Format as DATE

Biff

"mpierre" wrote in message
...
I have a column of dates in the format mm/dd/yyyy. I want to find the
first
occurence of the year 2005 in that column.
LOOKUP(2005,YEAR($A$2:$A$114),$A$2:$A$114) gives me the last occurence.
How
can I find the first occurence? Any ideas?

=INDEX(A2:A114,MATCH(2005,YEAR(A2:A114),0))

ctrl+shift+enter (not just enter)


"mpierre" wrote:

I have a column of dates in the format mm/dd/yyyy. I want to find the first
occurence of the year 2005 in that column.
LOOKUP(2005,YEAR($A$2:$A$114),$A$2:$A$114) gives me the last occurence. How
can I find the first occurence? Any ideas?

mpierre,

1. maybe your formula will work:, lookup( _ , _ , _) if the dates are sorted
in increasing order.
2. otherwise use auto filter. by making the date format as YYYY-mm-dd or
YYYY only, You can visibly find the location of the first occurence.



"mpierre" wrote:

I have a column of dates in the format mm/dd/yyyy. I want to find the first
occurence of the year 2005 in that column.
LOOKUP(2005,YEAR($A$2:$A$114),$A$2:$A$114) gives me the last occurence. How
can I find the first occurence? Any ideas?