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

  1. =INDEX(A:A, MATCH(FALSE, ISBLANK(A:A), 0)) // doesn't ignore errors

  2. =INDEX(A:A,MATCH(TRUE,INDEX((A:A<>0),0),0)) // ignore errors

  3. =INDEX(A:A,MATCH(TRUE,A:A<>"",0)) // ignore errors

  4. =VLOOKUP("*", A:A, 1,FALSE) // ignore errors

  5. =INDEX(A:A,MATCH(TRUE,INDEX(ISNUMBER(A:A),0),0)) // to get the first numeric value in Excel and ignore errors

  6. =INDEX(A:A,MATCH(TRUE,INDEX(ISTEXT(A:A),0),0)) // to get the first text value in Excel and ignore errors

  7. =MATCH(TRUE,ISERROR(A:A),0) // to get the first error position in Excel

  8. =MATCH(FALSE,ISBLANK(A:A),0) // to get the first non-blank cell position in Excel

    • A:A = Ranges

Scroll below for Google Sheets

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

Get the first non-empty cell in a column in Excel

How to get the first non-empty cell in a column in Excel?
Get the first non-empty cell in a column in Excel

GET THE LAST NON-EMPTY CELL IN A COLUMN — EXCEL FORMULA AND EXAMPLE

  1. =INDEX(A:A, MATCH(FALSE, ISBLANK(A:A), 0)) // doesn't ignore errors

  2. =INDEX(A:A,MATCH(TRUE,INDEX((A:A<>0),0),0)) // ignore errors

  3. =INDEX(A:A,MATCH(TRUE,A:A<>"",0)) // ignore errors

  4. =VLOOKUP("*", A:A, 1,FALSE) // ignore errors

  5. =INDEX(A:A,MATCH(TRUE,INDEX(ISNUMBER(A:A),0),0)) // ignore errors

    • above formula to get the first numeric value in Excel

  6. =INDEX(A:A,MATCH(TRUE,INDEX(ISTEXT(A:A),0),0)) // ignore errors

    • above formula to get the first text value in Excel

  7. =MATCH(TRUE,ISERROR(A:A),0)

    • above formula to get the first error position in Excel

  8. =MATCH(FALSE,ISBLANK(A:A),0)

    • above formula to get the first non-blank cell position in Excel

  • 2nd 3rd and 4th formulas will produce the same result

  • All formulas can be used in the ROW as well, except for VLOOKUP and change to HLOOKUP

  • 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 first non-empty cell in a column in Google Sheets

How to get the first non-empty cell in a column in Google Sheets?

Get the first non-empty cell in a column in Google Sheets

GET THE LAST NON-EMPTY CELL IN A COLUMN — GOOGLE SHEETS FORMULA AND EXAMPLE

  1. =INDEX(A:A, MATCH(FALSE, ISBLANK(A:A), 0)) // doesn't ignore errors

  2. =INDEX(A:A,MATCH(TRUE,INDEX((A:A<>0),0),0)) // ignore errors

  3. =INDEX(A:A,MATCH(TRUE,A:A<>"",0)) // ignore errors

  4. =VLOOKUP("*", A:A, 1,FALSE) // ignore errors

  5. =INDEX(A:A,MATCH(TRUE,INDEX(ISNUMBER(A:A),0),0)) // ignore errors

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

  6. =INDEX(A:A,MATCH(TRUE,INDEX(ISTEXT(A:A),0),0)) // ignore errors

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

  7. =ArrayFormula(MATCH(TRUE,ISERROR(A:A),0))

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

  8. =ArrayFormula(MATCH(FALSE,ISBLANK(A:A),0))

    • above formula to get the first non-blank cell position in Google Sheets

  • 2nd 3rd and 4th formulas will produce the same result

  • All formulas can be used in the ROW as well, except for VLOOKUP and change to HLOOKUP

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