Currently, I have a worksheet in Google Sheets that uses the formula:
=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, I want to select the columns A,B,D,Y,Z,AA,AB,AC instead.
So far, I've tried both of the following:
=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")
=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")
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}))
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: