Reference cell in a query in Google Sheets

0
votes

https://docs.google.com/spreadsheets/d/1ZupYqXhA-_ZSfuOUN70YuQnC9DUw7PUlrzv117knJ-I

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

6/29/2016 Deposit 480

6/24/2016 Deposit 359.61

8/8/2016 Deposit 11.11

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

1000000044 480

1000000045 359.61

201631212301237 11.11

In Column B on My Query Stinks, I want to enter a query. Here's what I'm trying: =query(Deposits!A2:C,"select A where C ='"& C4 &"'") For some reason, it returns #N/A Error Query completed with an empty output. I want it to find that 11.11 (the value in C4) matches 11.11 over on Deposits and return the date.

EDIT 1/15/2020: Updated the link to the Google sheets file.

1
google-sheets

1 Answers

2
votes

I think you've ended up with too many quotes in your formula. The following simplified version seems to work. 

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

Because of an extra set of quotes in the formula, I believe your original version ends up treating C4 as a text value instead of a cell address.

Additionally, it looks like that element opens with '" and then closes with "'" - so it's not balanced. I've had problems with auto-quoting like this in similar scenarios, especially on editing.