title: Combining LOOKUP and OFFSET
body: <p>I would like to offset the last entry in my data by one week. For example, I just created this example data:</p>
  
  <pre><code>day value
  1   4
  2   3
  3   5
  4   6
  5   1
  6   3
  7   9
  8   5
  </code></pre>
  
  <p>To find the last entry in the dataset, I use the lookup function:</p>
  
  <pre><code> =LOOKUP(9.99E+307,b1:b10)
  </code></pre>
  
  <p>which will return the value <code>5</code>. (In case that notation is not familiar, <code>9.99E+307</code> is the largest number that can be written in Excel).</p>
  
  <p>I would like then to compare this value to last week's value, and thus offset the last entry by 7. I see that OFFSET asks for: <em>offset(reference,rows,cols)</em> but using:</p>
  
  <pre><code>=OFFSET(LOOKUP(9.99E+307,b1:b10),-7,0)
  </code></pre>
  
  <p>does not seem to work (it returns an error).</p>
  
  <p>What could be the problem?</p>