< Go Back

Get the Value of the Last Non-empty Cell in Excel

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. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar