'Array Literal' Error in Google Sheets - Not getting the desired output-CodePudding

Here's the link to my trial sheet. Feel free to edit. The formula that I am trying to work on is in Master sheet cell A10. https://docs.google.com/spreadsheets/d/1skuD82oY9S976uPms1BvcwIrWshV2bKztSDDD76lofI/

I am trying to append certain data using arrays and query function. However, the formula throws an error saying "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows." Not really sure of what's causing the error. I have used the following formula in my actual sheet (different from trial one):

FILTER('CLASS2 - Overall List'!A2:B,'CLASS2 - Overall List'!E2:E>=B2,'CLASS2 - Overall List'!E2:E<=B3,'CLASS2 - Overall List'!C2:C=B16), 
IF(B4="CLASS1", 
FILTER('CLASS1 - Overall List'!A2:B,'CLASS1 - Overall List'!E2:E>=B2,'CLASS1 - Overall List'!E2:E<=B3,'CLASS1 - Overall List'!C2:C=B16), 
IF(B4="CLASS3", 
FILTER('CLASS3 - Overall List'!A2:B,'CLASS3 - Overall List'!E2:E>=B2,'CLASS3 - Overall List'!E2:E<=B3,'CLASS3 - Overall List'!C2:C=B16), 
IF(B4="All", 
QUERY({FILTER('CLASS2 - Overall List'!A2:B,'CLASS2 - Overall List'!E2:E>=B2,'CLASS2 - Overall List'!E2:E<=B3,'CLASS2 - Overall List'!C2:C=B16);FILTER('CLASS1 - Overall List'!A2:B,'CLASS1 - Overall List'!E2:E>=B2,'CLASS1 - Overall List'!E2:E<=B3,'CLASS1 - Overall List'!C2:C=B16);FILTER('CLASS3 - Overall List'!A2:B,'CLASS3 - Overall List'!E2:E>=B2,'CLASS3 - Overall List'!E2:E<=B3,'CLASS3 - Overall List'!C2:C=B16)})))))```

In the final step, I am just trying to append all the results from first 3 conditions - ie, Class1, Class2 and Class3. If I modify the formula to fetch only 1 column (A2:A instead of A2:B), it's working without any issue. Am I missing something here?

CodePudding user response：

Try

=IF(B3="ClassA",FILTER(ClassA!A2:B,ClassA!C2:C=B7,ClassA!E2:E>=B1,ClassA!E2:E<=B2),
IF(B3="ClassB",FILTER(ClassB!A2:B,ClassB!C2:C=B7,ClassB!E2:E>=B1,ClassB!E2:E<=B2),
IF(B3="All",query({
  iferror(FILTER(ClassA!A2:B,ClassA!C2:C=B7,ClassA!E2:E>=B1,ClassA!E2:E<=B2),{"",""});
  iferror(FILTER(ClassB!A2:B,ClassB!C2:C=B7,ClassB!E2:E>=B1,ClassB!E2:E<=B2),{"",""})
}),"where Col1 is not null")))

in case of error, you need to fill 2 cells by blanks or error message