How to find out index of nth empty row in Excel?-CodePudding

Given a column of numbers in A:A

I can find out that index of first empty row is 3 by =MATCH(TRUE,ISBLANK(A:A),0)

How can I change that formula to find out that index of second empty row is 6?

CodePudding user response：

You can try the following for your input data in cell C1:

=FILTER(ROW(A1:A7), ISNUMBER(XMATCH(ISBLANK(A1:A7), TRUE)))

Here is the output:

If you want only the 2nd blank or nth-empty in the more general case, then:

=SMALL(FILTER(ROW(A1:A7), ISNUMBER(XMATCH(ISBLANK(A1:A7), TRUE))),2)

You can also use the following alternative to the first formula:

=FILTER(IF(A1:A7="",ROW(A1:A7)), IF(A1:A7="",ROW(A1:A7))<>FALSE)

or using LET to avoid repetition:

=LET(x, IF(A1:A7="",ROW(A1:A7)), FILTER(x, x<>FALSE))