title: Find Newest Date In range defined by Offset
body: <p>I am trying to find the most recent date based on an offset range. It's kind of hard to explain so here is a screenshot of generic data to help (I couldnt find a file upload):</p>
  
  <p><a href="https://i.stack.imgur.com/SGhTT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SGhTT.png" alt="Example Data"></a></p>
  
  <p>I created a helper column and date diff. When the helper column hits 0, that indicates the start of the range that needs to be checked. I think using a form of offsets to define the range to be checked will work but I am not sure on how to construct it. It will need to define the range, then get the date from another offset for each line to compare and find the most recent date. It should end up looking like the highlighted rows. Is this even possible?</p>
  
  <p>Hopefully I have explained it adequately.</p>
  
  <p>Thanks!</p>
  
  <p>EDIT: I've been looking at it more and come up with the below formula:</p>
  
  <pre><code>=IF(B2 = SMALL(OFFSET(B2,0,0,2,1), 1),TRUE,FALSE)
  </code></pre>
  
  <p>Which is half the battle. Next is making sure the height of the offset is covering the range from 0 to the max of the helper column before it hits the next 0.</p>
  
  <p>SECOND EDIT: I've come up with the imperfect solution:</p>
  
  <pre><code>=IF(B2 = SMALL(OFFSET(B2,-C2,0,IF(C3 = 0, C2 + 1,MAX(C2:C5)+1),1), 1),TRUE,FALSE)
  </code></pre>
  
  <p>Issues with this is the Else statement in the height offset. I know that the MAX in the entire column is a value of 4. So I just set the else to check the next 3 rows it should cover that but it can potentially cause issues.</p>
  
  <p>Also where there are duplicate dates in the range, the SMALL() formula will not pick this up and it will be skipped entirely. Rare, but it happens. If anyone has anything better I am all ears!</p>