Creating Dynamic Array Formula in Google Sheets to Find Latest Category

Other Questions Google Sheets
1

I need help with a Google Sheets formula that’s driving me crazy. I have different sections in column A like (A / B / C) and similar. What I want to do in column B is create an array formula that looks at column A and finds the most recent section that was used.

The formula works fine when I copy it to each cell individually, but I need it to work as an array formula so it fills down automatically for the whole column.

Here’s what I’m trying:

=ARRAYFORMULA(
 LOOKUP(
 HUGE(1E+15),
 INDIRECT("A$1:A" & ROW()),
 INDIRECT("A$1:A" & ROW())
 )
)

Any ideas on how to make this work properly as an array formula?

2

the LOOKUP approach works, but you need to restructure it. try =ARRAYFORMULA(IF(A2:A=="","",INDEX(A$1:A2,COUNTA(A$1:A2)))) and drag down from B2. skip INDIRECT completely - COUNTA finds the last non-empty cell in your range.

3

The problem is ARRAYFORMULA doesn’t play nice with dynamic ranges from INDIRECT. I hit this same issue and switched to SCAN - it handles the row-by-row stuff way better. Try this instead:

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

SCAN goes through each cell and keeps the last non-empty value it finds. The LAMBDA checks if the current cell is empty - if not, that becomes the new value, otherwise it keeps the previous one. No need for LOOKUP/HUGE at all, and it works cleanly with ARRAYFORMULA since you’re not dealing with dynamic ranges. Way more reliable than trying to make INDIRECT work in arrays.

4

I’ve dealt with this exact problem before. Your issue is that INDIRECT and ROW() don’t work well together in ARRAYFORMULA - each row tries to reference a different range size and it breaks. Here’s what fixed it for me:

=ARRAYFORMULA(IF(A:A="","",INDEX(FILTER(A:A,A:A<>"",ROW(A:A)<=ROW(A:A)),COUNTIF(INDIRECT("A1:A"&ROW(A:A)),"<>"))))

This creates your dynamic range without the LOOKUP/HUGE mess. It filters non-empty cells up to the current row and grabs the last one. Instead of forcing LOOKUP to work with variable ranges, I’m using COUNTIF to find where the last non-empty cell sits. Drop this in B1 and it’ll fill down the whole column while keeping the dynamic behavior you want.