Does anyone know the formula to find the value of the last non-empty cell in a column, in Microsoft Excel?
Excel – Last non-empty cell in a column
excelworksheet-function
excelworksheet-function
Does anyone know the formula to find the value of the last non-empty cell in a column, in Microsoft Excel?
Best Answer
Using following simple formula is much faster
For Excel 2003:
It gives you following advantages:
Explanation:
(A:A<>"")
returns array{TRUE,TRUE,..,FALSE,..}
1/(A:A<>"")
modifies this array to{1,1,..,#DIV/0!,..}
.LOOKUP
expects sorted array in ascending order, and taking into account that if theLOOKUP
function can not find an exact match, it chooses the largest value in thelookup_range
(in our case{1,1,..,#DIV/0!,..}
) that is less than or equal to the value (in our case2
), formula finds last1
in array and returns corresponding value fromresult_range
(third parameter -A:A
).Also little note - above formula doesn't take into account cells with errors (you can see it only if last non empty cell has error). If you want to take them into account, use:
image below shows the difference: