Last value in column

[Manassasralph]

I have a column of values (Buy or Sell). There are blanks between some of the values.

I need to know the last value in the column.

I have Googled it, but I don't understand the explanations.

Any help is appreciated,

Manassasralph

 

[Manassasralph]

I wonder which would operate faster:

=LOOKUP(2,1/(V1:V5000<>""),V1:V5000)

=LOOKUP(9.99999999999999E+307,SEARCH("?",V1:V5000),V1:V5000)

Aladin, thank you for the post, but Jonmo1's solution works fine.

ManassasRalph

 

[abmati]

=LOOKUP(REPT("z",255),A:A)
This works ok for text, it gives last value of text, it does not work for numeric value. what is the formula for numeric values or mix (text & numeric)?

 

[Scott Huish]

Aladin's solution should work for you:

=LOOKUP(2,1/(V1:V5000<>""),V1:V5000)

 

[Aladin Akyurek]

=LOOKUP(REPT("z",255),A:A)
This works ok for text, it gives last value of text, it does not work for numeric value. what is the formula for numeric values or mix (text & numeric)?

The following three-cells approach keeps efficiency of:

LOOKUP(REPT("z",255),A:A)
LOOKUP(9.99999999999999E+307,A:A)

and kindred idioms with Match.

Thus...

D2:

=MATCH(REPT("z",255),A:A)

D3:

=MATCH(9.9999999999999E+307,A:A)

D4, the cell of the final result:

=INDEX(A:A,IF(COUNT(D2:D3)=2,MAX(D2:D3),SUMIF(D2:D3,">0")))

 

[T. Valko]

I wonder which would operate faster:

=LOOKUP(2,1/(V1:V5000<>""),V1:V5000)

=LOOKUP(9.99999999999999E+307,SEARCH("?",V1:V5000),V1:V5000)

In my tests the LOOKUP/SEARCH method is slightly faster.

However, differences are just about negligible and the difference ratio is uniform no matter what the range size. One version is not significantly faster over a larger range.

Tested on 5000 rows of data up to 65,000 rows of data.