How to find the next non blank cell and print the value in excel?

Question

Hi everyone,

I want to find the date for the next non blank cell based on column B, and print the date in column C.

For example, the next non blank cell after B3 is B4, so the formula will copy the date in cell A4 to cell C3.

Another example, the next non blank cell after B4 is B7, so the formula will copy the date in cell A7 to cell C4

Column E is the expected output for column C.

May I know how to achieve this without using any formula that is only available to office 365 user? Any help will be greatly appreciated! :)

Answer 1

Try below array formula for non 365 version of excel.

=IFERROR(IF(B3="","",INDEX($A$3:$A$14,SMALL(IF($B$3:$B$14<>"",ROW($A$3:$A$14)-ROW($B$2),""),COUNTA($B$3:$B3)+1))),"")

Press CTRL+SHIFT+ENTER to evaluate the formula as it is an array formula.