Get the last non-empty cell in a column in Excel and Google Sheets
=LOOKUP(2,1/(A:A<>""),A:A) // ignore errors
=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A) // doesn't ignore errors
=INDEX(A:A,MAX((A:A<>"")*(ROW(A:A)))) // doesn't ignore errors
=LOOKUP(2,1/(ISNUMBER(A:A)),A:A) // last numeric value in Excel and ignore errors
=LOOKUP(2,1/(ISTEXT(A:A)),A:A) // to get the last text value in Excel and ignore errors
=MAX(IF(ISERROR(A:A),ROW(A:A))) // to get the last error position in Excel
=LOOKUP(2,1/(A:A<>""),ROW(A:A)) // to get the last non-blank cell position in Excel and ignore errors
A:A = Ranges
For Google Sheets add ArrayFormula after =, an example below or scroll below for formulas
=ArrayFormula(LOOKUP(2,1/(A:A<>""),A:A))
Get the last non-empty cell in a column in Excel
How to get the last non-empty cell in a column in Excel?
GET THE LAST NON-EMPTY CELL IN A COLUMN — EXCEL FORMULA AND EXAMPLE
=LOOKUP(2,1/(A:A<>""),A:A) // ignore errors
=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A) // doesn't ignore errors
=INDEX(A:A,MAX((A:A<>"")*(ROW(A:A)))) // doesn't ignore errors
=LOOKUP(2,1/(ISNUMBER(A:A)),A:A) // ignore errors
above formula to get the last numeric value in Excel
=LOOKUP(2,1/(ISTEXT(A:A)),A:A) // ignore errors
above formula to get the last text value
=MAX(IF(ISERROR(A:A),ROW(A:A)))
above formula to get the last error position
=LOOKUP(2,1/(A:A<>""),ROW(A:A)) // ignore errors
above formula to get the last non-blank cell position
2nd and 3rd formulas will produce the same result
All formulas can be used in the ROW as well
Tested in Office 365 Excel version, if you have previous please use Ctrl+Shift+Enter
💡 Download the excel file used in this exercise here
Get the last non-empty cell in a column in Google Sheets
How to get the last non-empty cell in a column in Google Sheets?
GET THE LAST NON-EMPTY CELL IN A COLUMN — GOOGLE SHEETS FORMULA AND EXAMPLE
=ArrayFormula(LOOKUP(2,1/(A:A<>""),A:A)) // ignore errors
=ArrayFormula(LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A) // doesn't ignore errors
=ArrayFormula(INDEX(A:A,MAX((A:A<>"")*(ROW(A:A))))) // doesn't ignore errors
=ArrayFormula(LOOKUP(2,1/(ISNUMBER(A:A)),A:A)) // ignore errors
above formula to get the last numeric value in Google Sheets
=ArrayFormula(LOOKUP(2,1/(ISTEXT(A:A)),A:A)) // ignore errors
above formula to get the last text value in Google Sheets
=ArrayFormula(MAX(IF(ISERROR(A:A),ROW(A:A))))
above formula to get the last error position in Google Sheets
=ArrayFormula(LOOKUP(2,1/(A:A<>""),ROW(A:A))) // ignore errors
above formula to get the last non-blank cell position in Google Sheets
2nd and 3rd formulas will produce the same result
All formulas can be used in the ROW as well
💡 View the live Google Sheets used in this exercise here
- Extract left before first space
- Extract right after first space
- Extract the nth words in a text string
- Extract domain URL from a link
- Extract URL domain root
- Flip the first and last name
- Get the first non-empty cell in a column
- Extract text inside characters (parentheses)
- Get text between colons in Google Sheets
- Get text between comma in Google Sheets
- Sum by month
- SUMIF cells if contains part of a text string
- Sum total sales based on quantity & price
- Combine date and time
- Convert 1-12 to month name
- Dynamic current date and time
- Count blank cell
- Count cell between two values
- Combine two or more cell
- SUMIF formulas and example
- SUMIFS formulas and example
- COUNTIF formulas and example
- IF formulas and example
- TEXT formulas and example
- CONVERT formulas and example
- Change the negative number to zero
- Change negative to positive numbers
- Calculate percentage changes for containing zero or negative values
- Convert date to text
- Convert time to text
- Convert weekday string to number
- Combine two or more cells with a line break
- Combine two or more cells and transpose
- Count cells over 10 characters
- Count cells that are not blank
- Count if cells contain a specific text
- Count cells that begin with a specific text
- COUNTIF based on multiple criteria