Google-sheets – Trying to pull Duplicate data from multiple columns

google sheetsgoogle-sheets-arrayformulagoogle-sheets-query

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?

Best Answer

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:

Related Topic