This is with the assumption my data starts at row 2 (under my headers)
=arrayformula(if(istext(B2:B)*n(C2:C)*n(E2:E)*istext(F2:F),"Ok",if(len(B2:B)+len(C2:C)+len(D2:D)+len(E2:E)+len(F2:F),"MORE INFO",)))
Credit: GimelG from Google Docs Help Forum.
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.
Best Answer
...understanding the
ARRAY_LITERAL ERROR:until both queries/filters/formulas output something then all is good:
however if one of those queries/filters/formulas doesn't have anything to output it outputs
#N/A-No matches are found in QUERY/FILTER evaluation.- the issue is that#N/Ais only in the 1st cell:but array expects that matrix on both sides to be same (4 columns from both queries/filters/formulas):
so we wrap each query into
IFERRORand in case of error we output fake row with 4 fake columns -{"","","",""}- which will trick the array to output it like:therefore try like this: