How can I get ARRAYFORMULA to populate and entire column in Google Sheets?

Question

I'm trying to populate an entire column in Google Sheets without the need to copy the formula to all the cells. I got part way there, but now I'm stuck with a #VALUE! error.

The formula I have already is:

=ArrayFormula(IF(ISBLANK($B4),,SUM(VLOOKUP(FLATTEN(SPLIT($B4, ", ")),$G3:$H8,2,FALSE))+$H$10))

I've shared the spreadsheet it relates to.

So this takes a cell with comma separated products in it, SPLITs it on the comma to give me a row of cells, and then FLATTENs it to make it a column of cells. With this I do a VLOOKUP to identify the price, but in order to get the price for all the products I need to add an ArrayFormula at this point. That's fine, so far so good, then I wrap the result in a SUM so I can get the order total, add an additional cell and wrap the whole thing in an IF to ensure it only runs when column B is populated.

That can be seen in the example spreadsheet in cell C4, but I want to populate the whole of column C using ArrayFormula so I don't need to copy the formula all the way down, so I made an attempt in column D by changing the references to a specific cell (B4) to a range (B4:B) and I just get a value error relating to the split now.

=ArrayFormula(IF(ISBLANK($B4:$B),,SUM(VLOOKUP(FLATTEN(SPLIT($B4:$B, ", ")),$G3:$H8,2,FALSE))+$H$10))

Am I meant to be nesting multiple ArrayFormula calls? I've tried a number of things without any joy. Am I missing something obvious, other than my understanding of how ArrayFormula works that is?

player0 · Accepted Answer · 2022-12-05 02:19:48Z

2

use lambda:

=INDEX(BYROW(B4:INDEX(B:B, MAX(ROW(B:B)*(B:B<>""))), LAMBDA(b, 
 IFERROR(SUM(VLOOKUP(FLATTEN(SPLIT(b, ", ")), G3:H8, 2, ))+H10))))

answered Dec 5, 2022 at 2:19

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HuggieRich Over a year ago

Thanks for the reply player0. I realise there are many ways to go about things and probably no right answers as such, so is your solution just another way of doing it, or based on what I'm trying to achieve is it a better solution? Is what I'm trying to do not possible with ArrayFormula, or are you just giving me an alternative that will work just as well?

player0 Over a year ago

@HuggieRich its basically your formula just shortened. think of INDEX as shorter way how to type ARRAYFORMULA. and yes, there are multiple ways how to solve this, but this one is I guess shortest one. new LAMBDA function is here used to iterate over rows.

Collectives™ on Stack Overflow

How can I get ARRAYFORMULA to populate and entire column in Google Sheets?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related