I'm trying to work out a function in a Google Sheets cells to look at a column then search current row and "above" the current row to find a non number value (text). I have data that in two columns B (item code or category) & C (item description).

I need another column to contain the categories for each item - column D. I'm looking for a formula for this column, ideally an Arrayformula as the data can change, there can be multiple items per category, some might be only 1 item, some might be 100 items per category. The arrayformula in column D will get the category from column B if it is not a number.

B column - categories and item codes, C column - item descriptions, target is D column a copy of the categories from column B.

I've tried this numerous times and usually give up, do it manually but it becomes teadious quickly. Looking forward to any help that might come from this! thanks.

CodePudding user response：

In D2 try

=Arrayformula(if(isnumber(B2:B), vlookup(row(B2:B), filter({row(B2:B) , B2:B}, istext(B2:B)), 2), B2:B))

and see if that works?

CodePudding user response：

Try in D1

={"Category";ArrayFormula(lookup(row(B2:B),row(B2:B)/if(isnumber(B2:B),0,1),B2:B))}