Home > other >  Using IFS with Arrayformula function in Google Sheets
Using IFS with Arrayformula function in Google Sheets

Time:12-30

I was wondering if its possible to use an IFS function inside the arrayfunction?

Im looking to have a drop down list populate an array depending on what is selected in the data validation.

the formula im using is:

=ARRAYFORMULA(IFs(H2="1st Quarter",'Pool 1 data'!C4:AG34,H2="2nd Quarter",'Pool 1 data'!C38:AG68,H2="3rd Quarter",'Pool 1 data'!C72:AG102,H2="4th Quarter",'Pool 1 data'!C106:AG136))

H2 is the drop down validation. So when "1st Quarter" is selected I want it to return the range c4:AG34, and when "2nd Quarter" is selected to return the range C38:AG68 and so on.

The current formula doesn't return anything. Am I way off or is there a different approach to make this happen?

Any advice would by greatly appreciated!

EDIT: Here is the link to the spreadsheet

  • "converting" it into IFS() will return this (because cell A1 is not array/range):

  • now let's add an arrayformula (ranged input):

  • also note this behavior:

  • Related