Returning last value in a column

[saifsawafi]

Ive beentrying to write a function that retunrs the last none empty value in a column. note that some cells in same column will be empty. From the manual, the recommended function is =INDEX(A2:A30,MAX(ROW(A2:A30)×(A2:A30 《》""))). This assumes the range from A2 to A30 for example. However I try the function does not work. Any suggestion or help on this ?

[Glenn Kennedy]

If the column contains numbers:
=LOOKUP(1E+100,A:A)

If the column contains text:
=LOOKUP("zzzzz",A:A)

[saifsawafi]

If the column contains numbers:
=LOOKUP(1E+100,A:A)

If the column contains text:
=LOOKUP("zzzzz",A:A)

Thanks Glenn. will give it a try

[Tony Valko]

If the range may contain either text or numbers...

=LOOKUP(2,1/(A2:A30<>""),A2:A30)

[saifsawafi]

Hi Glenn, tried to apply what you recommended but did not wok the way I expect. The closest I was looking for is =LOOKUP(2,1/(A2:A30<>""),A2:A30).

Many thanks & regards

[saifsawafi]

Hi Tony,
Thanks a lot for your help and you have solved my problem.

Regards,
Saif

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.