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


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. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Hello i need a formula to pull the last non empty cell until the cell is no longer empty
Solved by A. C. in 12 mins
I am hoping someone can help me fix a problem with my classroom grade sheet (link below). I would like cell F4 to get the text contents of the last non-empty cell in column range M20:M300. This will be the student's latest comment. The problem I'm having is that this M20:M300 range is full of formulas, making any of the 'get last non-empty cell in a column' formulas ineffective. Thank you in advance for helping me solve this issue. https://docs.google.com/spreadsheets/d/1q6WQYtZ7hina2M34Cxw4yCKrHC9OM6JYeOYq0lNdBCc/edit?usp=sharing
Solved by Z. H. in 12 mins
Hello - In my Excel spreadsheet, I need to get the value of the non-blank cell in (non-adjacent cells) row. Here is a small example. Row # AD5 AI5 AN5 AS5 RESULT Cell Value AM AM Row # AD6 AI6 AN6 AS Cell Value BW BW
Solved by B. L. in 11 mins

Leave a Comment