filter/lookup question
 

Heres what I need
cell a5:a404 contains Names
cell b5:b404 contains Dates
cell c5:c404 contains Status

Example Data

Names Date Status
John 7/25/05 Active
Fred 7/28/05 Pending
Mike 8/12/05 Active

In cells a410:c412 i'd like to filter for the oldest active records.

Names Date Status
John 7/25/05 Active
Mike 8/12/05 Active

I can't just use the auto filter option, I need the values to be in that
specific cell range. Thanks for any help.

Hi!

Enter this formula in cell A411 as an array using the key combo of
CTRL,SHIFT,ENTER:

=INDEX(A$5:A$404,MATCH(LARGE(IF($C$5:$C$404="activ e",$B$5:$B$404),ROW($3:$3)-ROWS($1:1)),$B$5:$B$404,0))

Copy across to C411 then down to C412.

Format B411 and B412 as DATE.

Something tells me that there will be "more" to this than what was posted!

Biff

"Qaspec" wrote in message
...
Heres what I need
cell a5:a404 contains Names
cell b5:b404 contains Dates
cell c5:c404 contains Status

Example Data

Names Date Status
John 7/25/05 Active
Fred 7/28/05 Pending
Mike 8/12/05 Active

In cells a410:c412 i'd like to filter for the oldest active records.

Names Date Status
John 7/25/05 Active
Mike 8/12/05 Active

I can't just use the auto filter option, I need the values to be in that
specific cell range. Thanks for any help.