I want to sub in different arrays to a cell based upon the selection of a drop down menu-CodePudding

Assume I want a simple selection of “yes” or “no” in a drop down menu in google sheets. If yes, I want to take an array of 5 numbers from the c column. If no, I want to take an array of 5 numbers from the D column. My formula would look like this: =IFS(b1=“yes”, ARRAYFORMULA(c1:c5), b1=“no”, ARRAYFORMULA(d1:d5)) When I try this with an =IF instead of =IFS, for example:

=IF(B1=“yes”, ARRAYFORMULA(C1:C5)

It works. Any advice on how I should do this?

CodePudding user response：

Arrayformula should be outside:

=ARRAYFORMULA(IF(B1="yes", C1:C5, D1:D5))

If you want to use with IFS, all arguments should be of equal size:

=ARRAYFORMULA(IFS(IF(SEQUENCE(5),B1)="yes",C1:C5,IF(SEQUENCE(5),B1)="no",D1:D5))

The condition B1="yes" is repeated as a array using IF/SEQUENCE