title: How to Vlookup where column has value, not blank cells?
body: <p>I'm trying to lookup an ID from column I, in column A.</p>
  <pre><code>Dim x As Long
  
  lr = Worksheets(&quot;Risk Explorer greeks&quot;).Cells(Rows.Count, &quot;I&quot;).End(xlUp).Row
  
  Range(&quot;J2:J2&quot; &amp; lr).FormulaR1C1 = &quot;=VLOOKUP(RC[-1], R1C1:R50000C1, 1, False)&quot;
  </code></pre>
  <p>I have ~40,000 values in column J, however when I run this code it populates down to cell 237,000.</p>
  <p>How can I look up column J where it has a value, and not lookup loads of blank cells?</p>
  <p>Alternatively is there a faster way to do this lookup rather than the above formula?</p>