last negative cell

[Nikhil]

Hi.
I have a column that contain numbers (either + or -). I want to find the
last cell that contains a -ve value and return the corresponding date from
the adjoining column

e.g

Col A Col B
1-Apr-2010 -1000
2-Apr-2010 -1000
3-Apr-2010 +500
4-Apr-2010 -1000
5-Apr-2010 +700

In this illustration, i did like to have the value 4-Apr-2010 returned

Can anyone please help

Regards

Nikhil

 

[Billy Liddel]

Try this array formula (Entered with CTRL + SHFT + ENTER)

=INDEX(A:A,MATCH(MAX(B2:B65536<0),B2:B65536)+1)


Regards
Peter

 

[Teethless mama]

=LOOKUP(2,1/(B1:B100<0),A1:A100)