# Get the last non-empty cell in a column in Excel and Google Sheets

1. =LOOKUP(2,1/(A:A<>""),A:A) // ignore errors

2. =LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A) // doesn't ignore errors

3. =INDEX(A:A,MAX((A:A<>"")*(ROW(A:A)))) // doesn't ignore errors

4. =LOOKUP(2,1/(ISNUMBER(A:A)),A:A) // last numeric value in Excel and ignore errors

5. =LOOKUP(2,1/(ISTEXT(A:A)),A:A) // to get the last text value in Excel and ignore errors

6. =MAX(IF(ISERROR(A:A),ROW(A:A))) // to get the last error position in Excel

7. =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))

Check below for a detailed explanation with pictures and how to use formulas in Excel and Google Sheets.

# 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

1. =LOOKUP(2,1/(A:A<>""),A:A) // ignore errors

2. =LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A) // doesn't ignore errors

3. =INDEX(A:A,MAX((A:A<>"")*(ROW(A:A)))) // doesn't ignore errors

4. =LOOKUP(2,1/(ISNUMBER(A:A)),A:A) // ignore errors

• above formula to get the last numeric value in Excel

5. =LOOKUP(2,1/(ISTEXT(A:A)),A:A) // ignore errors

• above formula to get the last text value

6. =MAX(IF(ISERROR(A:A),ROW(A:A)))

• above formula to get the last error position

7. =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

# 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

1. =ArrayFormula(LOOKUP(2,1/(A:A<>""),A:A)) // ignore errors

2. =ArrayFormula(LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A) // doesn't ignore errors

3. =ArrayFormula(INDEX(A:A,MAX((A:A<>"")*(ROW(A:A))))) // doesn't ignore errors

4. =ArrayFormula(LOOKUP(2,1/(ISNUMBER(A:A)),A:A)) // ignore errors

• above formula to get the last numeric value in Google Sheets

5. =ArrayFormula(LOOKUP(2,1/(ISTEXT(A:A)),A:A)) // ignore errors

• above formula to get the last text value in Google Sheets

6. =ArrayFormula(MAX(IF(ISERROR(A:A),ROW(A:A))))

• above formula to get the last error position in Google Sheets

7. =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