CODEWITHSUNDEEP
google-sheetsgoogle-query-language
Neill
Neill

Reputation: 452

Query completed with an empty output

https://docs.google.com/spreadsheets/d/1033hNIUutMjjdwiZZ40u59Q8DvxBXYr7pcWyRRHAdXk

That's a link to the file in which it is not working! If you open it, go to sheet named "My query stinks".

The sheet called deposits has data like this in columns A (date), B (description), and C (amount):

+---+-----------+-----------------+---------+
|   |     A     |        B        |    C    |
+---+-----------+-----------------+---------+
| 1 | 6/29/2016 |      1000000044 |     480 |
| 2 | 6/24/2016 |      1000000045 |  359.61 |
| 3 | 8/8/2016  | 201631212301237 |   11.11 |
+---+-----------+-----------------+---------+

The sheet "My Query Stinks" has data in columns A (check number), B (failing query) and C (amount):

+---+-----------------+------+--------+
|   |        A        |  B   |   C    |
+---+-----------------+------+--------+
| 1 |      1000000044 | #N/A |    480 |
| 2 |      1000000045 | #N/A | 359.61 |
| 3 | 201631212301237 | #N/A |  11.11 |
+---+-----------------+------+--------+

In Column B on My Query Stinks, I want to enter a query. Here's what I'm trying: 

=query(Deposits!A:C,"select A where A =" &  A2)

For some reason, it returns "#N/A Error Query completed with an empty output." I want it to find that 1000000044 (the value in C4) matches 1000000044 over on Deposits and return the date.

Upvotes: 1

Views: 17297

Answers (3)

Grubbmeister
Grubbmeister

Reputation: 877

Another solution for this problem could be to replace '=' with 'contains':

=query(Deposits!A:C,"select A where B contains '" &A2&"'")

Simple, but this error cost me half a morning.

Upvotes: 0

Rene
Rene

Reputation: 1093

Try this:

=query(Deposits!A:C,"select A where B = '"&A2&"' LIMIT 1")

You'll need LIMIT 1 as you have multiple deposits for the same value in your second column.

Upvotes: 0

Wicket
Wicket

Reputation: 38284

Try

=query(Deposits!A:C,"select A where B ='" &A2&"'")

Explanation

Values like 1000000044 in Column B of the Deposit sheet and Column A of My Query Stinks sheets are set as text (string) values, so they should be enclosed on single quotes (apostrophes) otherwise QUERY think this values are numbers or variable names.

Upvotes: 1

Related Questions