last negative cell

N

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
 
B

Billy Liddel

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

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


Regards
Peter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top