I should know how to do this already, but I'm evidently having a brain cramp this morning.

I've got a worksheet with five columns -- the first is column is dates and the next four are corresponding numbers. A row gets appended to those columns each time inventory is reviewed.

What I'm aiming to do is to put a formula somewhere below the last row that will use the last value in the fifth column as part of its calculation. I'm having trouble this morning telling Excel to find and use that "last-value-in-a-column."

What am I overlooking?

Thanks, all.

Try this:


https://exceljet.net/formula/g...-last-non-empty-cell
A broader description is there.



Generic formula

=LOOKUP(2,1/(range<>""),range)

Summary

To find the value of the last non-empty cell in a row or column, even when data may contain empty cells, you can use the LOOKUP function with an array operation. The formula in F6 is:

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

The result is the last value in column B. The data in B:B can contain empty cells (i.e. gaps) and does not need to be sorted.

Note: This is an array formula. But because LOOKUP can handle the array operation natively, the formula does not need to be entered with Control + Shift + Enter, even in older versions of Excel.

Twenty minutes from "asked" to "answered." I love this place for that.

Thank you, sgalczyn, that's exactly what I needed.

Sorry for the lag - was brewing some good coffee..............

quote:

Originally posted by sgalczyn:
Sorry for the lag - was brewing some good coffee..............

Just asking, but wouldn't using =VLOOKUP be the preferred function? The result will likely be the same, but =VLOOKUP is the updated version of the =LOOKUP function.

quote:

Just asking, but wouldn't using =VLOOKUP be the preferred function? The result will likely be the same, but =VLOOKUP is the updated version of the =LOOKUP function.

tomaaaayto....tomahhhto

This message has been edited. Last edited by: sgalczyn,