title: How to set up an Array formula using Index/match in VBA to get first match cell
body: <p>I'm trying to set up array formula within a for loop to do a partial match of a string to a list of names on another worksheet called <a href="https://i.stack.imgur.com/TZH4b.jpg" rel="nofollow noreferrer">Project Name</a>. This should be the <a href="https://i.stack.imgur.com/FwCwr.jpg" rel="nofollow noreferrer">end product</a> I got the formula working in the spreadsheet using the method from <a href="https://exceljet.net/formula/get-first-match-cell-contains" rel="nofollow noreferrer">exceljet</a> but I ran into an error of "object required" when I tried to covert it to VBA. The cells(i,6) is the location of the string that I am trying to do a partial match to the project names. The column doesn't have to be "6", it is where the  Please help. Thanks!</p>
  
  <pre><code>Sub Shortname()
  
  Dim SRng As Variant
  Dim SName As Integer
  Dim SNrow As Integer
  Dim PLcol As Integer
  Dim PLrow As Integer
  
       Worksheets(3).Activate
  
       SNrow = Cells(Rows.Count, 1).End(xlUp).Row
  
       SRng = Range(Cells(2, 1), Cells(SNrow, 1)).Value
  
       Worksheets(2).Activate
  
       PLcol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
       PLrow = Cells(Rows.Count, 1).End(xlUp).Row
  
       For i = 2 To PLrow
  
           Cells(i, PLcol).Value = Application.WorksheetFunction.Index(SRng, Application.WorksheetFunction.Match("TRUE", Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(SRng.Value, Cells(i, 6))), 0), 1)
  
       Next i
  
  End Sub
  </code></pre>