1

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

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

0

1 Answer 1

1

For your formulas to work, you need to modify them

1. The correct ExchangeDaily named range should be A1:A9

2. The correct QUERY formula would be:

=QUERY('Exchange Water Report'!A1:E9,"select D where B contains 'Winchester (D5)' and A contains date'2020-04-03'",0)

When using date in a query QUERY it needs to follow the format yyyy-MM-dd

3. The correct VLOOKUP formula would be:

=IF(VLOOKUP(E44,{'Exchange Water Report'!$B$2:$B$9,ExchangeDaily},2,0)=H2,VLOOKUP(E44,{'Exchange Water Report'!$B$2:$B$9,ExchangeDaily},2,0),"")

The function VLOOKUP always searches from left to right. That is why we need to create a virtual range using curly brackets {}.

Functions used:

6
  • 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. Apr 30, 2020 at 1:47
  • The slips you made are quite common so it would be nice to also accept the answer (as mentioned before). Others can benefit as well. Glad I could help :) Apr 30, 2020 at 15:59
  • 1
    @HawkT Please read What should I do when someone answers my question?.
    – Rubén
    Apr 30, 2020 at 16:13
  • 1
    "Awesome! Thank you very much!!! I was able to use your corrected query function and it worked! ... Please read the answer to your thread! Still need some help understanding the IF statement." Please do not use the same question for an already working solution to ask another question. It completely throws future users off. You should just accept this one, post a new question and -if needed- refer to the older question. Apr 30, 2020 at 16:20
  • 1
    Both the vlookup and if statements display the proper values when used correctly and know what to expect from them. By radically editing again and again your original question can only be misleading and uncalled for. Please read about how this site works Apr 30, 2020 at 17:45

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.