first cell in a column with a specific value

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
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.
 
Back
Top