Google-sheets – How toterate between colunns and rows

When stacking arrays vertically, they must have the same number of columns. In case of an error, query outputs a single cell with #REF or #N/A or another message. You put iferror wrapper around it, but that only makes it so the output is one empty cell. Problem is, it is one cell and you need 8 columns to match the other array.

Solution: add a blank row with 8 cells as the second argument of iferror, to be used in case of errors.

=SORT(ArrayFormula({
 IFERROR(QUERY('5 Star Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, F, H, I 
   where A != ' ' AND F IS NOT NULL"),  
 {"","","","","","","",""}); 
 IFERROR(QUERY('5 Star Gear Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, G, H, I 
   where A != ' ' AND F IS NULL"),
 {"","","","","","","",""})
 }),7,FALSE)

Specifically, the error occurs because there is no sheet named '5 Star Gear Clubs (Hidden)'. Suggestion: when a formula throws an error, enter its parts in separate cells (without iferror wrappers) to see what they do.

Solved by Lance over on Google forums.

=ARRAYFORMULA(RegexReplace({
    Transpose(Split(TextJoin("@";1;IF(RegexMatch(IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);"^##1"); IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);));"@";1;1))\

    Transpose(Split(TextJoin("@";1;IF(RegexMatch(IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);"^##2"); IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);));"@";1;1))\

    Transpose(Split(TextJoin("@";1;IF(RegexMatch(IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);"^##3"); IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);));"@";1;1))\

    Transpose(Split(TextJoin("@";1;IF(RegexMatch(IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);"^##4"); IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);));"@";1;1))

};"##\d\s";))