title: excel: index and match with vba
body: <p>I have this formula on the first column in my sheet:</p>
  
  <pre><code>=IFERROR(INDEX(Plan2!$A$1:$K$20;MATCH(Plan3!B2;Plan2!$B$1:$B$20;0);MATCH(Plan3!$A$1;Plan2!$A$1:$K$1;0));"")
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/xjpVy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xjpVy.png" alt="enter image description here"></a></p>
  
  <p>And it fits perfectly for what I want: look up on Plan2 (my databse) for the information on column B of Plan3 by matching the result by matching the header of table.</p>
  
  <p>What I want know is to translate this to a VBA that do the same thing. This is what I've tried so far:</p>
  
  <pre><code>Sub AlocSubs()
  
  Dim i As Long
  Dim ws1 As Worksheet
  Dim ws2 As Worksheet
  
  Set ws1 = Sheets("Plan2")
  Set ws2 = Sheets("Plan3")
  
  For i = 2 To 20
      ws2.Cells(i, 1).Value = Application.WorksheetFunction.Index(ws1.Range("A1:K20"), .match(ws2.Range("B2"), ws1.Range("B1:B20"), 0), .match(ws2.Range("A1"), ws1.Range("A1:K1"), 0))
  Next i
  
  End Sub
  </code></pre>
  
  <p>When I try to run I get the message:</p>
  
  <blockquote>
    <p>Compilation error: Reference is not valid.</p>
  </blockquote>
  
  <p>And I get this line highlighted:</p>
  
  <pre><code>Sub AlocSubs()
  </code></pre>
  
  <p>This is the first time that I try to translate a formula do a code in VBA so I really don't know what is going wrong.</p>
  
  <p>Any suggestions will be appreciated.</p>