From time to time, we need a formula returning only the last value in a row range, when ranges grow rightwards, and daily, as in values for Monday thru Friday in a range. If there are dates in headers, it’s easy to test for latest date, but sometimes there’s no such help. The most concise I have found is the following:
The number 9.99E+307 is too large for Excel to process, and therefore isn’t found. When LOOKUP can’t find a lookup value in a range, it returns the last value found in that range. That’s what we have here in cell I1. Caveat: if there were a formula in cell H1 the example, and that formula returned a blank or a zero, cell, LOOKUP would return zero. So it’s best used with blank cells.
A formula returns zero in cell H3. This longer formula works correctly, because it tests each cell for zero, returning non-zero value if true. Value in H3 will only be returned if it were non-zero, so the formula correctly returns the last non-zero value (in G3).
The formula here is: