Search code examples
excelformulas

Excel find first blank cell

I am trying to find a value in a excel column which corresponds with the first empty value from another column and down one. So if D is the empty row then I want G

For example

A B C

D E

G H I

J K L

Would print G since the third column is where we are searching for the first null which is at line 2.

I tried =MIN(IF(A1:A4="", ROW(A1:C4))) which gets the right row but when I enter it in an index function it just gets me A if there is a null in the column.

I Have also tried =IFERROR(INDEX(A1:A4,1/MAX(INDEX((LEN(A1:D4)=0)/ROW(A1:D4),))),"No null") Which worked but didn't work if you have multiple empty cells in a column

Solution

  • Based on the clarification that the OP wants the subsequent row returned (not the one with the empty cell), another way to derive what QHarr showed but without the use of an array formula would be something like this:

    =INDIRECT("A"&INDEX(MAX((C1:C4="")*ROW(C1:C4))+1,1))

    or 

    =INDEX(A1:A4,INDEX(MAX((C1:C4="")*ROW(C1:C4))+1,1))

    If you don't know the last row, you could replace the ranges with a formula that determines the last row of data using an indirect cell reference like this:

    =INDIRECT("A"&INDEX(MAX((INDIRECT("C1:C"&INDEX(MAX((A:A<>"")*(ROW(A:A))),1))="")*ROW(INDIRECT("C1:C"&INDEX(MAX((A:A<>"")*(ROW(A:A))),1))))+1,1))

    or

    =INDEX(INDIRECT("A1:A"&INDEX(MAX((A:A<>"")*(ROW(A:A))),1)),INDEX(MAX((INDIRECT("C1:C"&INDEX(MAX((A:A<>"")*(ROW(A:A))),1))="")*ROW(INDIRECT("C1:C"&INDEX(MAX((A:A<>"")*(ROW(A:A))),1))))+1,1))

    I hope this helps.