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

  • 💡 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 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

Other useful extract formulas in Excel and Google Sheets explainedOthers FormulasDate and time functionsNumber based criteriaText-based criteriaExtract functions