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
=LOOKUP(2,1/(B:B<>""),B:B)
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.
Example
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.
=LOOKUP(2,1/(NOT(ISBLANK(B:B))),B:B)
We added the ISNUMBER function than to get the last numeric value, and the resulting formula was like this:
=LOOKUP(2,1/(ISNUMBER(B1:B100)),B1:B100)
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.
=LOOKUP(2,1/(B:B<>""),ROW(B:B))
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.
Leave a Comment