title: Excel VBA: VLookup on array from range as Variant throws "Type mismatch"
body: <p>Following Problem:
  I am running VLookups on big data (using <a href="https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/" rel="nofollow">double-true VLookup</a>), thus I would like to load the data into an array first.</p>
  
  <p>VLookup statement:</p>
  
  <pre><code>Application.VLookup(vLookUpKey, vLookUpRange, 1, True)
  </code></pre>
  
  <p>The following piece of code loads a range into the memory as array:</p>
  
  <pre><code>Dim vLookUpRange As Variant
  vLookUpRange = dataSheet.Range(dataSheet.Cells(2, 1), dataSheet.Cells(30000, 10)).Value
  </code></pre>
  
  <p>When defining the above lookup-range as Range it looks like this:</p>
  
  <pre><code>Dim vLookUpRange As Range
  Set vLookUpRange = dataSheet.Range(dataSheet.Cells(2, 1), dataSheet.Cells(30000, 10))
  </code></pre>
  
  <p>When it is defined as range the below VLookup runs without a problem (but slow, since I access the sheet all the time). If I use the version where it is loaded into an array as Variant it Throws a "Type mismatch" error. </p>
  
  <p>Any ideas what could be the problem? </p>