Search code examples
functionauto-updatearray-formulas

copy cell from above if cell to the left is empty (arrayformula)

Working sith google spreadsheet I'm trying to convert this formula

B2= IF ( A2="" ; B1 ; A2 )

to

B2= arrayformula( ? )

I have an arrayformula working in older spreadshhet, but it's not woring in the new one.

=ArrayFormula(IF(A2:A;A2:A;MMULT(MATCH(ROW(A2:A);FILTER(ROW(A1:A);LEN(A1:A)))=TRANSPOSE(MATCH(FILTER(ROW(A1:A);LEN(A1:A));FILTER(ROW(A1:A);LEN(A1:A));0));FILTER(A1:A;LEN(A1:A)))) )

Here is the example: __|____A___|____B___| 1 | data |function| 2 | 4 | 4 | 3 | | 4 | 4 | 1 | 1 | 5 | | 1 | 6 | | 1 | 7 | 5 | 5 | 8 | 7 | 7 | 9 | | 7 |

Can someone help me convert my normal function to an arrayformula function?

Many thanks.

Solution

  • Got my answer on productforums.google.com

    =ArrayFormula(if(row(A2:A) <= max(if(not(isblank(A2:A)); row(A2:A)));vlookup(row(A2:A);filter({row(A2:A)\A2:A};len(A2:A));2);))