Need help with formula for finding and returning the next non-blank cell in a range. Goal is to populate column B by formula.


A B
1 18246 18260
2 18260 18381
3
4 18381 18394
5
6
7 18394 18441
8 18441 18445
9
10 18445 18709




For each row, I need a formula in column B that pulls the next available number from a subsequent row in column A, regardless of how many blank rows there are between them -- there could be no blanks at all, or there could be many.

Ideally, I'd also like an if-then that says if there's no value in column A for a row, then there's no corresponding value in column B (i.e., leave the column B cell blank) so I can just copy and past the formula down all of column B and not have to got back and delete all the nulls.

I'd really appreciate any suggestions to make this work! It needs to be Excel 2007 compatible.

Thanks

=IFNA(IF(ISBLANK(A16),"",INDEX(A17:$A$100,MATCH(FA LSE,ISBLANK(A17:$A$100),0))),"")

make sure to do a CTRL+SHIFT+ENTER when you first paste it in.

This will find the next non empty cell in "A" and put it in "B" otherwise it will return nothing and when it gets to end it will leave nothing since there is not another

I am assuming that you won't have any more than 100 empty rows between numbers. If that is the case you can just increase that number or use something like =macth(9.99999999999999E+307,A:A,1) to get the last cell in your row.

Brilliant! Works like a charm -- thanks very much!!

I think I coppied the wrong cell. That was the formula I had in row 16 and for some reason FALSE has a space in it. I guess you figured that out.

=IFNA(IF(ISBLANK(A12),"",INDEX(A13:A110,MATCH(FALS E,ISBLANK(A13:A110),0))),"")

I also took out the $ since that would only allow for 100 rows max.

Yep, I fixed the space in "FALSE", and adjusted the range for my actual. I was going nuts trying to get the match syntax correct inside my version of the array. Your solution was spot on -- much appreciated!