Excel: Last Value in a Range
Find the last value in a row or range in Excel. Smart formulas using LOOKUP and nested IFs to return the last non-zero or non-blank value.
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:
=LOOKUP(9.99E+307,A1:H1)
ROW 1
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.
ROW 3
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:
=IF(H3=0,IF(G3=0,IF(F3=0,IF(E3=0,IF(D3=0,IF(C3=0,IF(B3=0,IF(A3=0,0,A3) ,B3),C3),D3),E3),F3),G3),H3)
Looking for a software partner?
Tell us about your project and we’ll show you how our team can help.
Found this useful? Share it.
