Share via

Excel: Find and Return the "last number," in a column, where the column contains: both blank cells and arbitrary, positive numbers (greater than zero).

Anonymous
2020-03-20T19:56:22+00:00

I have average experience using Excel but am unable to understand how to achieve the following:

  1. Regarding Column K, the range K12 through K377.
  2. All cells in the above range are either blank cells or the cells contain a positive number greater than zero, e.g., 14.37. (Note that the existence of blank cells and numbers in this range are completely arbitrary (without any logical, repetitive pattern).
  3. I am trying to write a formula (or use a function or macro) requesting the following:

A) Search the range K12 through K377.

B) Locate and identify the "last" number in the column. (NOTE: By "last" I mean the number located the closest to the bottom of the column's range. That is, the number closest to row 377, in column K.)

C) Whatever that number is, I want the number to be returned and displayed in a cell of my choosing, lets say cell K10.

D) Automatic Refresh: Ideally, anytime the column (column K) of numbers is edited (added to or subtracted from) I would like the column to be reevaluated by the formula, thus, potentially returning a new, more current, number to cell K10.

That's it.

If anyone could provide me with advise I would be quite grateful.

Regards,

MJ

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2020-03-20T21:24:17+00:00

    Hi MJ

    With this formula, you could return not only numbers but any string value. The last entered data value in that range.

    So Please, try this formula in K10 or in any other cell

    =LOOKUP(2,1/(K12:K377<>""),K12:K377)

    Notes:

    Change ranges according to your scenario.

    The picture below shows both cases

    Do let us know if you need more help

    Regards

    Jeovany

    8 people found this answer helpful.
    0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2020-03-20T22:04:04+00:00

    9.99999999999999E+307 is the largest number that can be entered in an Excel cell.

    LOOKUP searches for this number, but it can't find it, so it returns the last (bottommost) number that it encountered.

    3 people found this answer helpful.
    0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2020-03-20T20:59:34+00:00

    In K10:

    =LOOKUP(9.99999999999999E+307,K12:K377)

    2 people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-03-20T21:56:10+00:00

    In K10:

    =LOOKUP(9.99999999999999E+307,K12:K377)

    Hi HansV MVP

    This is absolutely awesome.

    I placed the formula accordingly and it returned the exact number I was looking for, it also updates when the data changes.

    I understand a good bit about formulas, but, the 9.99999999999999E+307 changes portion of the formula is new to me. I will have to study this further so that I can modify it and use it in the future.

    Thank you for your time, I am thrilled.

    Regards,

    MJ

    2 people found this answer helpful.
    0 comments