1 of 6
user avatar
user avatar

Google Sheets: How to populate a cell based on specific name and specific date using a query or if statement?

Hello new to the forum here and relatively new to all this data business. I feel pretty confident in my skills but this one has me stumped. Below you will see my initial query of dates, ditch_name(s),Flow, exchange water, comments. I've successfully populated this sheet to specifically research and find values from the sheet getting inputs weekly for these headings based on if there is a value for exchange water.

enter image description here

The next image is that of the report I want to populate. I have circled the cell red for this example. I need to keep track of the amount of water diverted specific date for a rotational schedule. Seeing that the initial query for the exchange water value is specific to the ditch name and the date, I am looking at populating this second form to reflect for the same. So the goal is to extract and populate F44 cell circled red) with the value of cell D2 in the initial query using the Ditch_name and Date. I'd like to be able to have this sheet populate on the fly if possible to reduce the errors that are associated with manually entering each dates exchange water value relative to the ditch that the water has been delivered to.

enter image description here

I've tried the following functions to attempt to achieve this:

=query('Exchange Water Report'!A1:E9,"select D where B contains 'Winchester (D5)'" "& select D where A contains '04/03/2020'",0) -Error (formula parse error)

=query(Exchange Water Report'!A1:E9,"select D where B contains 'Winchester (D5) (i've tried both and/or) and select D where A contains '04/03/2020'",0) -Error #VALUE!(unable to parse query string for function query parameter 2......)

Note: ExchangeDaily is named range within initial query Range A2:E8.


If I try the vlookup statement alone specific to the date it comes back with an error that it cannot find the numerical value associated with the date in the vlookup.

Hope I haven't made this too confusing. Thanks!