Formulas that you can use to get the value of the last non-empty cell in a range in Excel. These examples include simple formulas and more robust complex formulas that allow you to do things like account for errors, blank cells, get the last number, and return values that begin with certain text.
Simple Formula to Get Value of Last Non-Empty Cell
Most Robust Formula to Get Value of Last Non-Empty Cell
For this to work, there must be no blanks in the list of values; if that's an issue for you, look to the next section for the robust version of the formula.
=INDEX(A:A,COUNTA(A:A))
A:A represents column A, the column with the data. If you have a large data set, just use the first column of data as the reference.
Result:
When there are no blank cells or rows in the data set. This formula would not work correctly if we used it for the data in Column C.
The reason that this formula is included is because it is easier to remember and input and it will work for many data sets, especially data sets that are imported and that will never have blanks.
However, the next formula is more robust and will work even when your data set isn't perfect and contains blanks.
This works even if there are empty rows in the data set; however, it can be a bit confusing.
=LOOKUP(2,1/(C1:C50<>""),C1:C50)
This formula gets the value of the last non-empty cell from column C within the range of C1 to C50.
C1:C50 is the range in which your data set will appear. You can only use one column for reference in this formula, so do not include the reference of the entire range for a large table, just a single column. Also, both range references in the formula must be exactly the same.
When using this for your data, do not include the entire column reference like C:C because it will make the formula run slowly as it will have to cycle through every cell in the column, regardless of it it contains data or not; simply use a range that is large enough to always encompass your data set.
This is a confusing formula and, honestly, you don't need to read this section, just copy/paste the above formula to your worksheet, change the range references and use the result.
If the last cell in the range outputs an error and you want that to appear when it happens, use this version of the above formula:
=LOOKUP(2,1/(NOT(ISBLANK(C1:C50))),C1:C50)
C1:C50<>"" was replaced with NOT(ISBLANK(C1:C50))
Result:
Get the last value in the list that is a number.
=LOOKUP(2,1/(ISNUMBER(C1:C50)),C1:C50)
C1:C50<>"" was replaced with ISNUMBER(C1:C50)
This checks if the value in the cell is a number or not and uses that to create the TRUE/FALSE values.
=LOOKUP(2,1/(LEFT(C1:C50,1)="g"),C1:C50)
C1:C50<>"" was replaced with LEFT(C1:C50,1)="g"
This checks if the value in the cell starts with the letter g or not and uses that to create the TRUE/FALSE values.
As you can see, you can use the LOOKUP function to create a rather robust series of tests to get the last value from a list so long as that value meets certain criteria.
In the above examples, we used the ISNUMBER, LEFT, NOT, & ISBLANK functions to show you some of the variations that you can make, but you are not limited to these; play around with the function and see what kinds of results you can get, it can make your data reporting a lot more useful.
These formulas are not array formulas!
You can use the entire column range reference if you want, but it can really slow down the performance of your spreadsheet, so be careful with that; usually, it's best to use a range that is sufficiently large enough to encompass the entirety of your data, now and into the future, but not the entire column.
Make sure to download the attached spreadsheet so you can view these examples in Excel.