How can I create a dynamic array formula in Google Sheets to find the last non-empty value?

Other Questions Google Sheets
1

I’m seeking assistance in building an array formula that will auto-fill down without needing me to copy it to each individual cell.

Here’s what I have:

  • In column A, I list different section numbers (such as 1, 2, and 3)
  • I want column B to display the most recent section number from column A for each corresponding row

The formula I’m using works when pasted into individual cells but not as an array:

=ARRAYFORMULA(  
 INDEX(  
 INDIRECT("A$1:A" & ROW()),  
 MATCH(1E+308,INDIRECT("A$1:A" & ROW()))  
 )  
)

I’m facing a problem where this formula operates well when added to each cell separately, but it fails to auto-populate the entire column in array form. I would like it to dynamically adjust and fill the entire sheet without requiring any manual input. Can anyone offer advice on how to improve this setup?

2

ARRAYFORMULA breaks because it expects uniform operations, but your formula creates different range sizes for each row. I’ve hit this same issue tracking inventory across warehouses. Try SCAN instead - it handles dynamic accumulation way better:

=ARRAYFORMULA(IF(A:A="","",SCAN("",A:A,LAMBDA(acc,val,IF(val="",acc,val)))))

SCAN processes each cell one by one and keeps the last non-empty value as it goes down. No more row-dependent range issues since you’re not using INDIRECT references. You could also use MMULT with transpose operations, but that gets messy fast. SCAN works consistently even with huge datasets and skips all the lookup headaches that break your current formula in array context.

3

Your formula breaks because INDIRECT doesn’t play nice with ARRAYFORMULA when creating dynamic ranges for each row.

Honest advice? Stop wrestling with complex array formulas. I’ve been down this road - they turn into maintenance nightmares fast.

What you really need is automation that watches your sheet, spots changes in column A, and instantly updates column B with the last non-empty values. No more formula headaches or performance issues with big datasets.

I built something similar for project status tracking. It monitors the sheet, finds the most recent values, and writes them back automatically. Zero formulas to debug.

Best part? It scales perfectly. 100 rows or 10,000 - doesn’t matter. You can even add data validation or formatting without cluttering your spreadsheet.

Latenode makes this super easy. Connects straight to Google Sheets and handles all the processing logic.

4

Your INDIRECT function creates separate ranges for each row, which messes with how ARRAYFORMULA processes data. Ran into this exact issue building a project tracker last year.

This approach works way better with array formulas:

=ARRAYFORMULA(IF(ROW(A:A)=1,"Header",IF(A:A<>"",LOOKUP(2,1/(A$1:INDIRECT("A"&ROW(A:A))<>""),A$1:INDIRECT("A"&ROW(A:A))),"")))

LOOKUP beats INDEX/MATCH here and handles dynamic ranges better. The 2,1/ structure finds the last non-empty value more efficiently in array context.

I’ve also had success combining FILTER with SEQUENCE for this kind of dynamic behavior. Main thing is avoiding INDIRECT with row-dependent ranges inside ARRAYFORMULA - Google Sheets handles these differently than single-cell formulas.