# Return value in column A in preceding cells in column B using an array formula

How do you fill cells in column B (using an array formula) that are preceding the cell in column A with the value of column A only when cell in column C is not blank?

In other words, after column C is filled with text values, in the next empty row I enter a text value in column A, then I want the preceding cells in column B where column C is not empty to be filled with this manually entered value in column A, like this:

``````A   B   C
d   notblank
d   notblank
d   notblank
d
a   notblank
a   notblank
a   notblank
a   notblank
a
e   notblank
e   notblank
e
``````
• First, in a blank sheet, I enter values in column C manually let’s say into three rows. When finnished, in the next row I enter a value in column A also manually (“d” in example). Now this value should be automatically entered in the preceding cells in column B if there is a value in the adjacent cell in column C. I’d like to use an array formula for this in column B this way the formula can expand as more rows added. Then this automatic fill should be repeated when I enter new rows in column C and then a value in column A (“a” in example). Sep 22 '14 at 21:38

`=ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),{{0;FILTER(ROW(A:A),LEN(C:C)=0)},{FILTER(A:A,LEN(C:C)=0);""}},2),))`
`=ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),FILTER({ROW(A:A),A:A},LEN(C:C)=0),2),))`