Solved: A simple formula would probably do the trick

[speedy dave]

Hi guys (again)
I am trying to figure out a formula to enter into a cell that retrieves the last entry in a column.
I have a formula... ( its not VBA but..... )

[VBA]

=INDEX('list'!D,MATCH(9.99999999999999E+307,'list'!D))

[/VBA]

As you can see, the formula looks up and copies the last entry in column D ( of the worksheet "list" ) and pastes it to the cell that the formula sits in.

And it works fine...... but it will only retrieve "numbers"

I need to be able to retrieve "words"

What can I substitue the "9.99999999999E+307" with to return the text that is the last entry of that column ???

[ZVI]

Hi Dave,

Try for numbers:

=LOOKUP(9.99999999999999E+307,List!D:D)

For English text:

=LOOKUP(REPT("z",255),List!D:D)

For text in other languages:

=LOOKUP(REPT(CHAR(255),255),List!D:D)

Vladimir

[speedy dave]

Hi Vladimir, brilliant !
A nice easy simple solution, I love it.

Would it be ok if you could explain the syntax used ??

ie, the 9.99999999999999E+307, ( what would just 9.9 do ? )

and the "z" and the 255

etc

What does it all mean ???

[ZVI]

Dave,

This is the solution for the last text or numbers in the range from D1 upto D65000:

 =LOOKUP(2,1/(List!D1:D65000<>""),List!D1:D65000)

As to your question, 9.99999999999999E+307 is the largest number available for Excel.
And the REPT("z",255) means 255 concatenated chars of "z", it's the biggest English word vs any other English text.

Below are the links to the best explanation of Aladin Akyurek:
Lookup for a value in Unsorted data
9.9999999 .... ?
vlookup with multiple matches - which match is returned

Regards,
Vladimir

[speedy dave]

Thanks heaps Vladimir
I am starting to get the hang of all this !
It's brilliant !!

Cheers for all your help

[ZVI]

Happy to be helpful, Dave!
Cheers,
Vladimir