PDA

View Full Version : Solved: A simple formula would probably do the trick



speedy dave
04-14-2010, 05:36 PM
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..... )



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



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
04-14-2010, 06:22 PM
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
04-14-2010, 06:36 PM
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
04-14-2010, 06:53 PM
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 (http://www.mrexcel.com/forum/showthread.php?p=492425)
9.9999999 .... ? (http://www.mrexcel.com/forum/showthread.php?t=102091)
vlookup with multiple matches - which match is returned (http://www.mrexcel.com/forum/showthread.php?t=310278)

Regards,
Vladimir

speedy dave
04-14-2010, 07:12 PM
Thanks heaps Vladimir
I am starting to get the hang of all this !
It's brilliant !!

Cheers for all your help :hi:

ZVI
04-14-2010, 07:21 PM
Happy to be helpful, Dave!
Cheers, :beerchug:
Vladimir