Find the first empty cell in the same column/row

Question

I am trying to work out a formula that will give me the row number of the first empty cell in a column. Currently I am using:

=MATCH(TRUE, INDEX(ISBLANK(A:A), 0, 0), 0)

This works fine, unless the formula is put in the same column as the column I am searching in, in which case it does some sort of circular reference or something. Is there a formula I can use instead which will work when placed in the same column as it searches in?

Solution 1

Another way to do it

=MIN(IF(A2:A6="",ROW(A2:A6)))

you have to press CTRL+SHIFT+ENTER

The difference is that this will give you the Row number of the first empty cell The previous answer will give the position (how many rows from the starting row) of the first empty cell... Both ways are valid depending on your needs

Solution 2

All you have to do is count the non blank cells in a column : COUNTA e.g.

=COUNTA(E:E)

If you want to find the first blank cell from further down the column, then set the column range e.g. E23:E100 and add the cell number -1 to it.

=COUNTA(e23:E1000)+23-1

Comments

[ejlj]

You are right, I just provided a different way of doing it.

[pstraton]

Excellent example of KISS principle!