first cell in a column with a specific value

[Guest]

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?

 

[T. Valko]

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)=2005,0))

Format as DATE

Biff

 

[Guest]

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

ctrl+shift+enter (not just enter)

 

[Guest]

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.