Formula for last Non Blank Cell.

[tesh13]

Is there a formula which reads over a certain coloumn range and return the value of the last cell which is not blank. i.e. D4 contains a word, D5 contains a word, d6 is blank, d7, d8, d9 contains a word and d10 is blank.

Currently i'm using the formula:
=INDEX(D4:D10,COUNTA(D4:D10),1)

But its returning the value in D8 not D9. How can this be rectified?

 

[NBVC]

Try this:

=SUMPRODUCT(MAX((D4:D10<>"")*D4:D10))

 

[Oaktree]

=lookup(rept("Z",255),D4:D10)

 

[tesh13]

Thanks guys,

Oaktree thanks very much, formula works

NBVC, the formula didnt work because the cell contained words/text..apologise for not being clear on this.

 

[NBVC]

Sorry. Should have spotted that.