title: Explain LOOKUP formula
body: <p>I'm trying to understand some legacy Excel file (<strong>it works</strong>, but I would really like to understand how/why it's working). </p>
  
  <p>There is a sheet for data input (<code>input sheet</code>)and some code that is called to process data in the <code>input sheet</code>. I found out that number of rows in the <code>input sheet</code> is determined using a Lookup formula like this:</p>
  
  <p><code>=LOOKUP(2;1/('Input sheet'!E1:E52863&lt;&gt;"");ROW(A:A))</code></p>
  
  <ol>
  <li>"E" column contains names for import items and column is <strong>NOT</strong> sorted</li>
  <li>"A" column does not contain anything special - I can replace it with B, C or whatever column and it does not affect the formula's outcome</li>
  </ol>
  
  <p>According to what I have found about Lookup behaviour: <code>•If the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.</code></p>
  
  <p>What does this ^-1 operation to the specified range? If E(x) is not empty -> it should turn into 1, but if it is empty - then it would be 1/0 -> that should produce <code>#DIV/0!</code> error...</p>
  
  <p><code>1/('Input sheet'!E1:E52863&lt;&gt;"")</code></p>
  
  <p>The outcome is the same, if I replace 2 with any positive number (ok, tried only some, but it looks like this is the case). If I change lookup value to 0, then I get #N/A error -> <code>•If the value is smaller than all of the values in the lookup_range, then the LOOKUP function will return #N/A</code></p>
  
  <p>I am stuck... can anyone shed some light?</p>