1

https://docs.google.com/spreadsheets/d/1VSiKZbD_PoQTfbav7spalkyBgrt4T8juaLlKOM7LgLM/edit#gid=0

I have two columns, A and B. Throughout this project, the same name will appear in each column. I want to be able to select the name from the dropdown list, and have it return the proper data.

I have the dropdown list working, the list contains all the names that appear in each column.

When I select "Mike", it returns the proper info, being that Mike appears in A and in B. But when I select "John" or "Danny", I get the error message In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

The formula I have in E8 is

={QUERY(A1:B3,"SELECT * WHERE A contains """&E2&"""");QUERY(A1:B3,"SELECT * WHERE B contains """&E2&"""")}

How can I rectify this?

0
0

To avoid the error message you could wrap your formula with the IFERROR function:

=IFERROR({QUERY(A1:B3,"SELECT * WHERE A contains """&E2&""""); 
         QUERY(A1:B3,"SELECT * WHERE B contains """&E2&"""")}, 
         "No match")

enter image description here

EDIT

Ok, but there is supposed to match, I'm sorry I'm confused. I'm not looking to just eliminate the error message. Since "John" appears in ColumnA, why is it No Match?

Though the OP's closing question is "How can I rectify this?", I will follow the comment and explain.

What you want to do using the curly brackets {}, is to have your results displayed one under the other, like ={1;2}

+---+---+------------+--------+--------+
| A | B |  formula   | result | result |
+---+---+------------+--------+--------+
| 1 | 2 | ={1,2;3,4} |        |        |
| 3 | 4 |            |      1 |      2 |
|   |   |            |      3 |      4 |
+---+---+------------+--------+--------+

Only in this case, as an example for John, because your second query completes with an empty output it is as if you are asking for:

+---+---+------------+---------+
| A | B |  formula   | result  |
+---+---+------------+---------+
| 1 | 2 | ={1,2;3, } | #ERROR! |
| 3 |   |            |         |
+---+---+------------+---------+

which will result in an #ERROR!.
Because you do not have simple numbers but handle queries, you get

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

Functions used:

4
  • Ok, but there is supposed to match, I'm sorry I'm confused. I'm not looking to just eliminate the error message. Since "John" appears in ColumnA, why is it No Match? – user247176 May 21 '20 at 21:26
  • Please check the edited answer. – marikamitsos May 21 '20 at 22:37
  • Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos May 21 '20 at 22:55
  • I see you read my answer. I hope my explanation helped you realize why this particular formula produces the error. In any case let us know. – marikamitsos May 22 '20 at 1:24

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.