Formula to find value in last cell in column with numerical data NOT a formula

Enter using Ctrl-Shift-Enter: This will find the last value in column B that is not "" - it could be anything:

=IFERROR(INDEX($B$1:$B$1000,MAX(IF($B$1:$B$1000<>"",ROW($B$1:$B$1000)))),"")

To return the last number, again, with C-S-E:

=IFERROR(INDEX($B$1:$B$1000,MAX(IF(ISNUMBER($B$1:$B$1000),ROW($B$1:$B$1000)))),"")

LOOKUP, using Binary Search, finds last value <= criteria -- it assumes data to be sorted (regardless of reality), and will ignore data types that differ to the data type of the criteria.

so, in _Roots suggestion:

- criteria is numeric type (strings {incl. null} are ignored)
- the criteria is big (bigger than any value in your dataset)

LOOKUP thus returns last numeric value from your range as it deems this to be the biggest (assumes sorted), and still <= criteria.

similar approach can be used with Text (to a point) - e.g.

=LOOKUP(REPT("Z",255),$B:$B)

would return last text string (but this would include null strings) -- note the 255 char limitation

there are then variants to handle non-blanks/nulls

=LOOKUP(2,1/($B:$B<>""),$B:$B)

but the above is more expensive, computationally speaking, than the others.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.