  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.

# 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 ## Subscribe to Excelchat.co Another blog reader asked this question today on Excelchat: