title: Convert vlookup to more than 255 characters via Excel VBA
body: <p>I am looking for reverse vlookup with more than 255 characters in Excel VBA.
  This is the formula based one which I took from this website.</p>
  <p><code>=INDEX(F2:F10,MATCH(TRUE,INDEX(D2:D10=A2,0),0))</code></p>
  <p>I have try to convert it in VBA. Here below sample code</p>
  <pre><code>Sub test()
  
  'concat
  Range(&quot;i1&quot;) = WorksheetFunction.TextJoin(&quot; &quot;, True, Range(&quot;g1:h1&quot;))
  
  'lookup
  Sal1 = Application.WorksheetFunction.Index(Sheets(&quot;sheet1&quot;).Range(&quot;a1:a2&quot;), Application.WorksheetFunction.Match(True, Application.WorksheetFunction.Index(Sheets(&quot;sheet1&quot;).Range(&quot;i1:i1&quot;) = Range(&quot;i1&quot;).Value, 0), 0))
  '=INDEX($W$3:$W$162,MATCH(TRUE,INDEX($W$3:$W$162=U3,0),0))
  
  
  End Sub
  
  </code></pre>
  <p>It works well but it didn't when i change the <code>range(&quot;i1:i1&quot;)</code> to <code>range(&quot;i1:i2&quot;)</code></p>