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.




How do I get this to work?


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


  • 3
    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
  • 1
    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
  • Hi. I know this solution and it's very useful. I wonder if we can somehow avoid making additional row. When it's in the middle of stack, it makes empty row. Normally you can eliminate it using =query(table;"select * where Col1 is not null"). But I have a problem with this. My table have a mix of data types and QUERY messes up my data (assumes for example, that all the rows should be numbers and deletes string values). I've illustrated my problem here: stackoverflow.com/questions/69978650/… Nov 17 '21 at 15:11

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.