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

Question

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?

Whenever possible, you need to include a minimal example that reproduces the issue. You can also include the expected behavior, share a sample sheet, that would be helpful as well. Please visit How to Ask have some tips on how to write a question, so the community will be able to help you out in a better way. — Lorena Gomez
– Lorena Gomez, Commented Jun 24, 2022 at 2:02
Hey yes, have edited the post to include the trial worksheet. — Gitz
– Gitz, Commented Jun 24, 2022 at 4:51

Mike Steelson · Accepted Answer · 2022-06-24 06:30:28Z

2

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

answered Jun 24, 2022 at 6:30

Mike Steelson

15.4k2 gold badges7 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related