Get the first non-empty cell in a column in Excel and Google Sheets
=INDEX(A:A, MATCH(FALSE, ISBLANK(A:A), 0)) // doesn't ignore errors
=INDEX(A:A,MATCH(TRUE,INDEX((A:A<>0),0),0)) // ignore errors
=INDEX(A:A,MATCH(TRUE,A:A<>"",0)) // ignore errors
=VLOOKUP("*", A:A, 1,FALSE) // ignore errors
=INDEX(A:A,MATCH(TRUE,INDEX(ISNUMBER(A:A),0),0)) // to get the first numeric value in Excel and ignore errors
=INDEX(A:A,MATCH(TRUE,INDEX(ISTEXT(A:A),0),0)) // to get the first text value in Excel and ignore errors
=MATCH(TRUE,ISERROR(A:A),0) // to get the first error position in Excel
=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 LAST NON-EMPTY CELL IN A COLUMN — EXCEL FORMULA AND EXAMPLE
=INDEX(A:A, MATCH(FALSE, ISBLANK(A:A), 0)) // doesn't ignore errors
=INDEX(A:A,MATCH(TRUE,INDEX((A:A<>0),0),0)) // ignore errors
=INDEX(A:A,MATCH(TRUE,A:A<>"",0)) // ignore errors
=VLOOKUP("*", A:A, 1,FALSE) // ignore errors
=INDEX(A:A,MATCH(TRUE,INDEX(ISNUMBER(A:A),0),0)) // ignore errors
above formula to get the first numeric value in Excel
=INDEX(A:A,MATCH(TRUE,INDEX(ISTEXT(A:A),0),0)) // ignore errors
above formula to get the first text value in Excel
=MATCH(TRUE,ISERROR(A:A),0)
above formula to get the first error position in Excel
=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 LAST NON-EMPTY CELL IN A COLUMN — GOOGLE SHEETS FORMULA AND EXAMPLE
=INDEX(A:A, MATCH(FALSE, ISBLANK(A:A), 0)) // doesn't ignore errors
=INDEX(A:A,MATCH(TRUE,INDEX((A:A<>0),0),0)) // ignore errors
=INDEX(A:A,MATCH(TRUE,A:A<>"",0)) // ignore errors
=VLOOKUP("*", A:A, 1,FALSE) // ignore errors
=INDEX(A:A,MATCH(TRUE,INDEX(ISNUMBER(A:A),0),0)) // ignore errors
above formula to get the first numeric value in Google Sheets
=INDEX(A:A,MATCH(TRUE,INDEX(ISTEXT(A:A),0),0)) // ignore errors
above formula to get the first text value in Google Sheets
=ArrayFormula(MATCH(TRUE,ISERROR(A:A),0))
above formula to get the first error position in Google Sheets
=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
- 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 last 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