1

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?

| improve this question | |
2

...understanding the ARRAY_LITERAL ERROR:

  • until both queries/filters/formulas output something then all is good:

    0

  • 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:

    e

  • but array expects that matrix on both sides to be same (4 columns from both queries/filters/formulas):

    0

  • 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:

    0

  • therefore try like this:

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

| improve this answer | |
  • 2
    Wow, that makes a lot of sense, thank you. I'm going to build you a shrine. – Ceebee86 Jul 3 '19 at 12:48
  • Hi User0, Im getting the same issue with this formula? I've used your advice, but I can't see what I am missing. docs.google.com/spreadsheets/d/… – Ceebee86 Jul 9 '19 at 8:37
  • not sure what are you after but correct syntax should be: ={"header";TRANSPOSE(FILTER(Sheet1!A2:A,Sheet1!H2:H=A2))} – user0 Jul 9 '19 at 10:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.