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: