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.