[SOLVED] "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows." error with stacked queries when any of the results is empty

"In ARRAY_LITERAL, an Array Literal was missing values for one or more rows." error with stacked queries when any of the results is empty

I have a cell formula like

={QUERY(...);QUERY(...)}

The first query always return results but the second may or may not return a result based on the underlying data and the filter conditions (in my case there are actually more than 2 stacked queries any of which except the first may give empty results).

This works when both queries return some results. However, if the second query returns no results based on the selected filters, it gives the error In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

I verified each query separately and when verified in isolation, the second query gives Query completed with an empty output for the filters which give the ARRAY_LITERAL error. This confirms that the query itself is not invalid in any other way.

So, how can I make this work without any error when any of the queries in the stacking give empty results?

Solution

Use the VSTACK function:

=VSTACK(QUERY(), QUERY())

You may also want to wrap it in an IFNA to hide the errors.

=IFNA(VSTACK(QUERY(), QUERY()))

This formula will only hide the errors, i.e. it will replace them with a blank cell. To actually remove them, you can use something like this:

=LET(
  v, VSTACK(QUERY(), QUERY()),
  FILTER(v, 1-ISNA(INDEX(v,,1)))
 )