CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Kelvin Castelino
Kelvin Castelino

Reputation: 153

Multiple ArrayFormula in the same formula in Google Sheets

Can ArrayForumla be used multiple times in a single formula?

=ARRAYFORMULA(INDEX('Form Responses 2'!A2:AN2,ARRAYFORMULA(MAX(COLUMN('Form Responses 2'!K2:AN2)*(--('Form Responses 2'!K2:AN2<>""))))))

I want the formula after ArrayFormula to be applied to all new rows when data is added or captured.

Currently, when I have tried, it's not giving me any output or error.

Upvotes: 0

Views: 1586

Answers (1)

Kelvin Castelino
Kelvin Castelino

Reputation: 153

So I later found the solution i.e.,

=ARRAYFORMULA(IF(LEN(J2:J), VLOOKUP(J2:J,QUERY(SPLIT(FLATTEN('Form Responses 2'!K1:AN1&"_"&'Form Responses 2'!K2:AN),"_"), "Where Col2 <>''", 0), 2, 0),))

Working Example

We can have multiple array formulas, but we can't have nested arrays. Having several ARRAYFORMULA is the same as just doing one ARRAYFORMULA on the top level.

That being said, INDEX function in Google Sheets doesn't handle arrays. It does in Excel. In this case, MAX shouldn't return an array so it wouldn't matter.

Upvotes: 1

Related Questions