Find First Cell with Any Value – Excel & Google Sheets

This tutorial will demonstrate how to find the first non-blank cell in a range in Excel and Google Sheets.

find first cell with any value MAIN Function

Find First Non-Blank Cell

You can find the first non-blank cell in a range with the help of the ISBLANK, MATCH, and INDEX Functions.

find-first cell with any value 01

Note: This is an array formula. If you are using Excel 2019 or earlier, you must enter the formula with CTRL + SHIFT + ENTER instead of just ENTER.

Let’s see how this formula is built up.

ISBLANK Function

The ISBLANK Function analyzes every cell in the B3:B11 range and returns an array of TRUE and FALSE values. In our example it would look like this:

 

MATCH Function

With the MATCH Function we determine the position of the first TRUE value in the array created by the ISBLANK Function.

find first cell with any value Match

 

INDEX Function

Using the result of the MATCH Function we can determine with the INDEX Function the row of the first non-blank cell in the range and return its value.

find first cell with any value INDEX

Substituting the Cell D3 reference with our MATCH formula give us our original formula:

 

Reminder: This is an array formula. If you are using Excel 2019 or earlier, you need to press CTRL + SHIFT + ENTER to enter the formula, instead of simply pressing ENTER.

Find First Cell With Any Value in Google Sheets

This formula works the same in Google Sheets except that it’s not necessary to hit CTRL + SHIFT + ENTER.

find first cell with any value Google Function

 

 

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!