title: MAXIFS with ARRAYFORMULA not working in Google Sheets
body: <p>Please help wonderful people!</p>
  
  <p>I'm trying to use a MAXIFS formula within an ARRAYFORMULA in Google Sheets to obtain the latest date in column A from sheet 1, where the value in column A of sheet 2 matches in column E in sheet 1. Here's my formula:</p>
  
  <pre><code>=ARRAYFORMULA(IF(ISBLANK(A2:A),"",IF(MAXIFS(Sheet1!$A:$A,Sheet1!$E:$E,A2:A)=0,"",MAXIFS(Sheet1!$A:$A,Sheet1!$E:$E,A2:A))))
  </code></pre>
  
  <p>Sheet 2:</p>
  
  <p><img src="https://i.stack.imgur.com/iyfiU.png" alt="Screen shot of the formula in sheet 2"></p>
  
  <p>Sheet 1:</p>
  
  <p><img src="https://i.stack.imgur.com/9xDkZ.png" alt="Screen shot of the lookup data in sheet 1"></p>
  
  <p>I then repeat these in the 6 further columns to give the previous 6 dates using an extra criteria in the MAXIFS formula where I ask for the latest date, less than the previous column. Formula as follows:</p>
  
  <pre><code>=ARRAYFORMULA(IF(ISBLANK(A2:A),"",IF(MAXIFS(Sheet1!A:A,Sheet1!E:E,A2:A,Sheet1!A:A,"&lt;"&amp;B2:B)=0,"",MAXIFS(Sheet1!A:A,Sheet1!E:E,A2:A,Sheet1!A:A,"&lt;"&amp;B2:B))))
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/5GALC.png" alt="Screen shot of sheet 2 and the above formula setup"></p>
  
  <p>It all works on the first row but every row after that gives the same results suggesting the formula must be using the value in A2 as the criterion in the MAXIFS statement on all subsequent rows.</p>
  
  <p>I can't see what I've got wrong so it makes me think MAXIFS in ARRAYFORMULA may not work...</p>
  
  <p>Please help</p>