title: Using XLOOKUP via VBA
body: <p>I ran into trouble running a bit more complex Excel XLOOKUP formula over VBA. I have a plain XLOOKUP running perfectly fine in both Excel spreadsheet as well as VBA:</p>
  <pre><code>=XLOOKUP(1234,Orders[Number],Orders[Comment],&quot;&quot;,0,1)
  MsgBox Application.WorksheetFunction.XLookup(1234, Range(&quot;Orders[Number]&quot;), Range(&quot;Orders[Comment]&quot;), &quot;&quot;, 0, 1)
  </code></pre>
  <p>However I need to add one extra condition. It works fine in Excel spreadsheet:</p>
  <pre><code>=XLOOKUP(1234&amp;9,Orders[Number]&amp;Orders[Class],Orders[Comment],&quot;&quot;,0,1)
  </code></pre>
  <p>But I can't manage to convert this to VBA code as the code below produces an error:</p>
  <blockquote>
  <p>Run-time error '13': Type mismatch error</p>
  </blockquote>
  <p>The latest version I tried with was this. I have a feeling the problem is somehow concating the ranges?</p>
  <pre><code>MsgBox Application.WorksheetFunction.XLookup(1234 &amp; 9, Range(&quot;Orders[Number]&quot;) &amp; Range(&quot;Orders[Class]&quot;), Range(&quot;Orders[Comment]&quot;), &quot;&quot;, 0, 1)
  </code></pre>