ArrayFormula to Find Previous Non-Empty Cell in Google Sheets

google sheets

In Google Spreadsheets, without using Apps Script etc., is it possible to achieve the following with a single formula, such as arrayformula (or maybe mmult or something strange, but no fill-down) at the top of Column B?:

scenario:

Column A:
cells are empty or non-empty

goal:

Column B:
contains the last non-empty cell of column A

example:

input | output
===============
    a | a
------+--------
      | a
------+--------
      | a
------+--------
    5 | 5
------+--------
      | 5
------+--------
    c | c
------+--------
    c | c
------+--------
      | c
------+--------
    a | a
------+--------

Best Answer

Short answer

=ArrayFormula(vlookup(ROW(1:10),{IF(LEN(A1:A10)>0,ROW(1:10),""),A1:A10},2))

Explanation

Assuming the input data in the Column A of the below table and that the above formula is in cell B1.

Note: In order to make easier to evaluate the formula, the Column A (input) doesn't repeat values but it will work with any kind of values (letters, numbers, symbols, repeated, unsorted, etc.)

      |    A       B   
  === + ======= =======
   1  |    a       a   
   2  |            a   
   3  |    b       b   
   4  |            b   
   5  |            b   
   6  |    c       c   
   7  |    d       d   
   8  |    e       e   
   9  |            e   
  10  |    f       f

VLOOKUP was selected as it could be used to find approximate values and it could return multiple values (array).

As the input values could appear in any order, instead of using the actual values, the row number was used for non empty cells and empty cells have assigned an empty string "".

It was used LEN instead of ISBLANK because cells with formulas that return an empty string return FALSE and this could cause problems in some common scenarios.

IF(LEN(A1:A10)>0,ROW(1:10),"")

NOTE: In you want to save three characters, the second parameter ("" the character is the parameter separator ,) could be removed. When analysing this part of formula empty cells will return FALSE.

As VLOOKUP requires that the lookup up column be the first column, instead of a range, a "semi-manually" made array was used.

{IF(LEN(A1:A10)>0,ROW(1:10),""),A1:A10}

The above approach save us cells and processing time.

Related Topic