Have you been searching around for a clean and effective way to get the value of the last non-empty cell in Excel? Fortunately, there is a simple way to do this using the LOOKUP function. Here is an explanation as well as an example.
Find value of the last non-empty cell
Formula using LOOKUP
Explanation of formula
This non-array and the non-volatile formula are great to use the lookup function and find out the value of the last non-empty cell in a row or column. Here is how you can apply this formula to a situation.
Using B:B<>”” a particular array of TRUE and FALSE values is set up and then the lookup value is found.
Figure 1. Get value of last non-empty cell by LOOKUP formula
We used the following formula in the above-mentioned example. The original formula modified to this form as it removes any errors that might be there in the last non-empty cell. ISBLANK is used with NOT to work around the errors which exist in there.
We added the ISNUMBER function than to get the last numeric value, and the resulting formula was like this:
Then the following formula is used to find out the position (i.e., the row number or the column name) of the last non-empty cell.
This way we find out the position of the row or column number/name of the last non-empty cell by feeding the corresponding row/column number/name in the lookup function. So, follow the instructions carefully and perform step by step in Excel to get the value of the last non-empty cell.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.