Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles Miscellaneous Get the Value of the Last Non-empty Cell in Excel

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:
Solution examples
I have a column of data lets say in P:P, I used the formula =LOOKUP(2,1/(NOT(ISBLANK(P:P))),P:P) to return the last value in this data. However now I need another formula to return another value from the same selection that is not the result of the above formula. Do you know if I can do this via formulas?
Solved by C. J. in 21 mins
I have a cell that has a working vlook formula in it. When it looks up source data it returns blank. That is fine. I now want to use an IF formula to say if that cell is blank leave the other cell blank and if not then show value in different cell. The issue I have is that when the IF formula looks up the cell it reports back FALSE due to the vlook up formula being in the cell. I want the IF formula to show nothing instead of FALSE. Can you help?
Solved by Z. E. in 60 mins
need help =IF(C2="","",IF(ISBLANK(IF(C2="yes",HLOOKUP(VALUE(MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2)),Sheet2!$B$1:$AA$1000,2,FALSE),IF(C2="no",HLOOKUP(VALUE(MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2)),Sheet2!$B$1:$AA$1000,3,FALSE),""))),"NOT YET",IF(C2="yes",HLOOKUP(VALUE(MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2)),Sheet2!$B$1:$AA$1000,2,FALSE),IF(C2="no",HLOOKUP(VALUE(MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2)),Sheet2!$B$1:$AA$1000,3,FALSE),""))))
Solved by G. S. in 9 mins
need help =IF(C2="","",IF(ISBLANK(IF(C2="yes",HLOOKUP(VALUE(MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2)),Sheet2!$B$1:$AA$1000,2,FALSE),IF(C2="no",HLOOKUP(VALUE(MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2)),Sheet2!$B$1:$AA$1000,3,FALSE),""))),"NOT YET",IF(C2="yes",HLOOKUP(VALUE(MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2)),Sheet2!$B$1:$AA$1000,2,FALSE),IF(C2="no",HLOOKUP(VALUE(MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2)),Sheet2!$B$1:$AA$1000,3,FALSE),""))))
Solved by E. U. in 12 mins
VLOOKUP help. Trying to input data from database into new table
Solved by S. Q. in 40 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc