Define a range by searching for first and last?

[Gadget]

I need a formula that searches a column of log file dates for the first and
last entry in a month and returns the address of the first and last.

Example:
A1 2-30-09
A2 2-31-09
A3 2-31-09
A4 3-3-09
A5 3-4-09
A6 3-26-09
A7 3-27-09
A8 4-2-09

If I asked it for March, it should return "A4:A7" or "A4", "A7".

 

[smartin]

I need a formula that searches a column of log file dates for the first and
last entry in a month and returns the address of the first and last.

Example:
A1 2-30-09
A2 2-31-09
A3 2-31-09
A4 3-3-09
A5 3-4-09
A6 3-26-09
A7 3-27-09
A8 4-2-09

If I asked it for March, it should return "A4:A7" or "A4", "A7".

Maybe this array* formula will help:

=ADDRESS(MATCH(3,MONTH(A1:A8),0),1)&":"&ADDRESS(MATCH(3,MONTH(A1:A8),1),1)

Where [MATCH(3,] means March. Your list must be sorted for this to work.

*Commit array formula by pressing Ctrl+Shift+Enter, not just Enter.

 

[Steve Yandl]

If the values are in A1:A8, this would return the address where the earliest
date and the latest dates are separated by a comma (but no quote marks as
you show). If you really want the quotes, it would be an easy modification.

= "A"&MATCH(MIN(A1:A8),A1:A8,0)&", "&"A"&MATCH(MAX(A1:A8),A1:A8,0)

Steve Yandl