Using IFS with Arrayformula function in Google Sheets
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
https://docs.google.com/spreadsheets/d/1fIRpI_f76ac4VNcpp0Ffvx_68lRLjFtaPrYFdgFEDs0/edit?usp=sharing
use:
=ARRAYFORMULA(
IF(H2="1st Quarter",'Pool 1 data'!C4:AG34,
IF(H2="2nd Quarter",'Pool 1 data'!C38:AG68,
IF(H2="3rd Quarter",'Pool 1 data'!C72:AG102,
IF(H2="4th Quarter",'Pool 1 data'!C106:AG136, )))))
see: https://webapps.stackexchange.com/a/124685/186471
you may think that the behavior of IFS() is similar to IF() and the reason of having IFS() is to avoid nesting of multiple IF() but that's not so true. Yes, there are some common baselines however, there is a major difference when it comes to arrays. IFS() in a combination of arrayed output expects arrayed input - that's why you got returned single-cell output instead of arrayed output.
- let's say your
IF()formula is like:
- "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:
- How to combine multiple conditions in a PowerShell if-statement
- I get an error when I end the if statement with a concluding else statement
- Does PHP have short-circuit evaluation?
- Why is this code still adding 1 to i even when the if statment that does so isnt called in C?
- error compiling in c user input
- R Conditional IfElse With Random Values
- Can not get past illogical line pep8 error
- smarty help with if statement
- 'if' must have both main and 'else' branches if used as an expression
- What is the standard for choosing "when" vs "if/else" in Kotlin?
- Python script continuously repeats the first if - else statement
- Why does python use 'else' after for and while loops?
- Use of ternary operator instead of if-else in C++
- Evaluate boolean environment variable in Python
- How to use if/else statement in terraform for loop
- change number grade to letter grade
- How to debug this substring matching code?
- If condition is getting skipped even if the condition is true
- Assign value to all rows based on condition being fulfilled or not
- Looping through a list in Python
- Why does if (fork() || fork() || fork()) behave differently?
- Python if not == vs if !=
- Discord.py | If statement not working correctly
- Angular 17 - In @if syntax, how to store returned value from a function in a variable and use it in the same @if block
- Angular: ng-content doesn't render with *ngIf then else
- How to get the next Bi-weekly date based on a given start date and today's date in Google Sheets
- What is the best way to refactor an if else branch
- Unreachable code in if-else structure
- Python if/else/elif
- Ternary conditional operator in Scala