Creating an Array Formula in Spreadsheet for Automatic Category Retrieval

Other Questions Google Sheets
1

I’m working on a spreadsheet and need help with a formula. Here’s what I’m trying to do:

Column A has different categories (like 1, 2, 3). I want column B to automatically show the last used category from column A.

I’ve got a formula that works when I copy-paste it into each row, but I want it to fill the whole column automatically. Here’s what I’ve tried:

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

Can someone help me turn this into an array formula that fills down the whole column B without needing to copy-paste? Thanks!

2

hey emcarter, i think i got a solution for ya. try this formula in B1:

=ARRAYFORMULA(IF(LEN(A:A), LOOKUP(ROW(A:A), ROW(A:A)*(A:A<>“”), A:A), “”))

this should auto-fill column B with the last used category from A. lemme know if it works for u!

3

I’ve dealt with similar issues in my spreadsheets before. Here’s a trick that’s worked well for me:

=ARRAYFORMULA(IF(ROW(A:A)=1, “Category”, IF(A:A<>“”, A:A, IFNA(VLOOKUP(ROW(A:A), {ROW(A:A), A:A}, 2, 1)))))

This formula does a few things:

  1. Sets a header in B1
  2. Copies the category if there’s one in column A
  3. Looks up the last non-empty value above if A is empty

It’s been pretty reliable in my experience, even with large datasets. The IFNA function helps avoid those pesky #N/A errors. Give it a shot and see if it solves your problem!

4

I’ve encountered a similar challenge in my work with spreadsheets. Here’s an alternative approach that might suit your needs:

=ARRAYFORMULA(IF(ROW(A:A)=1, “Last Category”, IF(A:A<>“”, A:A, VLOOKUP(ROW(A:A), FILTER({ROW(A:A), A:A}, A:A<>“”), 2, 1))))

This formula populates the entire column B. It labels the header as “Last Category” and then fills each row with either the category from column A (if present) or the most recent non-empty category above it.

The FILTER function creates a dynamic range of non-empty categories, which VLOOKUP then uses to find the last applicable category for each row. This method is efficient and should work well for large datasets.