CODEWITHSUNDEEP
excelformulas
User
User

Reputation: 363

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

Upvotes: 3

Views: 1834

Answers (3)

FocusWiz
FocusWiz

Reputation: 533

I am still not sure that we are answering the correct question.

My understanding is that =MIN(IF(A1:A4="", ROW(A1:C4))) which you indicate returns the correct row doesn't do anything like what I thought you were asking for. I am not sure that I understand what you were trying to achieve.

Likewise, when I use:

=IFERROR(INDEX(A1:A4,1/MAX(INDEX((LEN(A1:D4)=0)/ROW(A1:D4),))),"No null")

I get "A"

Are you looking for the first column of the row after the row with a blank in the third column or are you looking for the first column of a row with any empty cell (including the first one) or something else?

Upvotes: 1

FocusWiz
FocusWiz

Reputation: 533

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.

Upvotes: 2

QHarr
QHarr

Reputation: 84465

I would do the following array formula:

=INDEX(A1:A4,MIN(IF(C1:C4="",ROW(C1:C4)))+1)

Entered with Ctrl + Shift + Enter

Data:

Data

Upvotes: 0

Related Questions