Find the first empty cell in the same column/row

excel excel-formula match cell
DylanJW picture DylanJW · Jul 30, 2014 · Viewed 54.7k times · Source

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?

Answer

ejlj picture ejlj · Jul 30, 2014

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

Related questions

Shortcut to Apply a Formula to an Entire Column in Excel

If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows! Is …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More
Simple Pivot Table to Count Unique Values

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on. For instance, I have this: ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456 What …

Read More