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

Example

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

Generic Formula

{=MATCH(TRUE,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 blank cell.

What It Does

This formula will return the position of the first 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 TRUE value in our Boolean array which corresponds to the first blank cell in the range.

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

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

MATCH(TRUE,{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},0) then looks for the first exact match to a TRUE value within this array. This returns the value 3 since the first TRUE value is in the 3rd position and this is our first blank cell 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.