title: How to use LOOKUP from two different sheets?
body: <p>I have a Calc document that has N sheets. First sheets are equal and contain some data. Last sheet is a summary.</p>
  
  <p>My problem is that I need to use the LOOKUP function in the last sheet to look up for data in the N-1 previous sheets. This doesn't work:</p>
  
  <pre><code>=LOOKUP(X, Sheet1.A1:Sheet9.A100, Sheet1.B1:Sheet9.B100)
  </code></pre>
  
  <p>In this case it's valid a solution that requires to hardcode the sheets, i.e.:</p>
  
  <pre><code>=LOOKUP(X, FANTASTIC_JOIN_FUNCTION(Sheet1.A1:A100;Sheet2.A1:A100;Sheet3.A1:A100; ... ), FANTASTIC_JOIN_FUNCTION(Sheet1.B1:B100;Sheet2.B1:B100;Sheet3.B1:B100; ...))
  </code></pre>
  
  <p>but I don't know which is this <code>FANTASTIC_JOIN_FUNCTION</code>...</p>
  
  <p>Any idea?</p>