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

Given a column of numbers in A:A

11
22

33
44

55

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?

>Solution :

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:
sample output file

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

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

Leave a Reply