title: Array cell reference in where clause of query
body: <p>Google Sheets sample file:
  <a href="https://docs.google.com/spreadsheets/d/1WqqiysWdYMAop5uQXZnisuX291wY2PFmAMEKZhya2IM/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1WqqiysWdYMAop5uQXZnisuX291wY2PFmAMEKZhya2IM/edit?usp=sharing</a></p>
  <p>I am trying to calculate the average value of each person for each month. My two variables to match are the names and month of the date. Currently I am able to get the correct values if I am applying the formula to each cell using this formula:</p>
  <p><code>=IFERROR(QUERY(Sheet2!A:C,&quot;Select avg(C) where A = '&quot;&amp;A2&amp;&quot;' and month(B) = month(date '&quot;&amp;TEXT(DATE(2021,MONTH(DATEVALUE($A$1&amp;&quot;1&quot;)),1),&quot;yyyy-mm-dd&quot;)&amp;&quot;') label avg(C) '' &quot;),&quot;-&quot;)</code></p>
  <p>What I want to do is be able to do an arrayformula instead so I will only need to input the formula once and wont have to worry about the number of names entered. I tried using this formula:</p>
  <p><code>=ARRAYFORMULA(IFERROR(QUERY(Sheet2!A:C,&quot;Select avg(C) where A = '&quot;&amp;A2:A&amp;&quot;' and month(B) = month(date '&quot;&amp;TEXT(DATE(2021,MONTH(DATEVALUE($A$1&amp;&quot;1&quot;)),1),&quot;yyyy-mm-dd&quot;)&amp;&quot;') label avg(C) '' &quot;),&quot;-&quot;))</code></p>
  <p>, but it only calculates the average for that row.</p>
  <p>If possible, the solution should also be using query since in the data I will be using it on I am not able to use sumifs/averageifs for some reason.</p>