title: How can I use the OFFSET worksheet function in VBA?
body: <p>I want to create a custom worksheet function that emulates the vlookup-match-offset combination and accepts only 3 arguments: the vertical lookup value, the horizontal lookup value, and the array containing the return values and header/left margin. If those values are represented by v, h, and r, I want the custom function to emulate:</p>
  
  <pre><code>=vlookup(v,r,match(h,offset(r,0,0,1),0),false)
  </code></pre>
  
  <p>The code I tried to write is as follows (using Excel 2010):</p>
  
  <pre><code>Function xlookup (v as Variant, h as Variant, r as Range) as Variant
      xlookup = Application.WorksheetFunction.Vlookup(v, r, _
          Application.WorksheetFunction.Match(h, _
          Application.WorksheetFunction.Offset(r,0,0,1),0),false)
  </code></pre>
  
  <p>Unfortunately, I don't think <code>Offset</code> is a method of <code>WorksheetFunction</code>. What's the best way to return the first row of a range as a range?</p>