How To Find The Position Of The First Non Blank Cell In A Range

Example

{=MATCH(FALSE,ISBLANK(B3:B9),0)}

Generic Formula

{=MATCH(FALSE,ISBLANK(Range),0)}

Note: This is an array formula. Do not type out the {} brackets. Hold Ctrl + Shift then press Enter while in Edit Mode to create an array formula.

Range – This is the range in which you want to find the position of the first non blank cell.

What It Does

This formula will return the position of the first non blank cell within a given range.

How It Works

First we create an array of Boolean values with ISBLANK(Range). The values in this array will either be TRUE if the corresponding cell in the Range is blank or FALSE if the corresponding cell in the Range is not blank. Then we use the MATCH function to find the first FALSE value in our Boolean array which corresponds to the first non blank cell in the range.

In our example ISBLANK(Range) creates the following array.

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}

MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE},0) then looks for the first exact match to a FALSE value within this array. This returns the value 4 since the first FALSE value is in the 4th position and this is our first non blank cell is in the range.

About the Author

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.