How to create 2 query options inside an IF function in google sheets-CodePudding

I am trying to create an if function and if true execute one query if false execute another query, but I keep on having a 'Array Literal was missing values for one or more rows' error, the funtion goes like this:

=ARRAYFORMULA({"Cases";IF(H2="SFDC",QUERY(OPTION1),QUERY(OPTION2))})

Any ideia why, is there any other way to construct a conditional like this?

Thanks in Advance.

CodePudding user response：

You need to make sure that our output as the same number of columns. For example, in your equation, you have one column of data (the header "Cases"). Because of this, the result of your IF statement must also have only one column of data.

One way to fix this is to define some extra empty columns. For example:
=ARRAYFORMULA({"Cases","","";IF(H2="SFDC",QUERY({1,2,3},"select *"),QUERY({4,5,6},"select *"))})

I added two more columns after Cases, therefore allowing my resulting query to properly expand.

CodePudding user response：

This error is produced When two arrays have different numbers of columns, in you case make sure the length of array in QUERY(OPTION1) is the same length as the one in QUERY(OPTION2).
use this formula to check array length. for QUERY(OPTION1)

=COUNTA(SPLIT(QUERY(OPTION1),"/"))

and this for QUERY(OPTION2)

=COUNTA(SPLIT(QUERY(OPTION2),"/"))

Replace it with you formula of course.

CodePudding user response：

I managed to solve the issue, I was not specifying the header at the end of the query which was generating a conflict. Thanks to all.