2

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

1 Answer 1

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, 2020 at 21:26
  • Please check the edited answer. May 21, 2020 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. May 21, 2020 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. May 22, 2020 at 1:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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