In ARRAY_LITERAL, an Array Literal was missing values for one or more rows

Question

I've been trying to get this array formula to work, but I get an error. It's just a vlookup array to display multiple columns. It works fine without the {""; bit, but I want it to drop down one row.

={"";ARRAYFORMULA(IFERROR(VLOOKUP($AA$4:$AA,CUSTOMER_DATA,{2,3,4,5,6},FALSE),""))}

Without

=ARRAYFORMULA(IFERROR(VLOOKUP($AA$4:$AA,CUSTOMER_DATA,{2,3,4,5,6},FALSE),""))

How do I get this to work?

Answer 1

...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/A is 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 IFERROR and 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:

  ={{"","","","",""}; 
   ARRAYFORMULA(IFERROR(VLOOKUP(AA4:AA, CUSTOMER_DATA, {2,3,4,5,6}, 0)))}