So right now i have a worksheet in my google sheet that uses
=query('Form Responses 1'!A2:AC,"Select A,B,D,L,M,N,O,P Where L is not null")
This selects the columns A,B,D,L,M,N,O,P Where L is not null but if L is null and nothing happens i want to select the columns A,B,D,Y,Z,AA,AB,AC
And i cant seem to figure it out for some reason lol. Thanks in advance for the help
I tried
=query('Form Responses 1'!A2:AC,"Select A,B,D,L,M,N,O,P Where L is not null" AND "Select A,B,D,Y,Z,AA,AB,AC Where Y is not null")
Which doesnt work.
=query('Form Responses 1'!A2:AC,"Select A,B,D,L,M,N,O,P Where L is not null" OR "Select A,B,D,Y,Z,AA,AB,AC Where Y is not null")
CodePudding user response：
Answer
The following formula should produce the result you desire:
=ARRAYFORMULA(IF(ISBLANK(L2:L),{A2:A,B2:B,D2:D,Y2:Y,Z2:Z,AA2:AA,AB2:AB,AC2:AC},{A2:A,B2:B,D2:D,L2:L,M2:M,N2:N,O2:O,P2:P}))
Explanation
First, the =ISBLANK function is used to check whether the value of column L is empty or not. This creates an array of boolean values which are fed into the =IF function. Depending on the result of =ISBLANK one of two possible sets of columns are selected.
Everything is contained within =ARRAYFORMULA so that it functions properly across many rows.
Functions used: