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. 

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

avatar