title: Apply formula by calling function
body: <p>I need help on my issue.</p>
  
  <p>I have an Excel file with two sheets named as:</p>
  
  <p>1.Hold</p>
  
  <p><a href="https://i.stack.imgur.com/i4d1a.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/i4d1a.png" alt="Hold worksheet"></a></p>
  
  <p>2.20Apr'20 </p>
  
  <p><a href="https://i.stack.imgur.com/XRfe5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XRfe5.png" alt="enter image description here"></a></p>
  
  <p>I want to find 'all reason of the id' using lookup. The expected output as below:</p>
  
  <p><a href="https://i.stack.imgur.com/jLiK8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jLiK8.png" alt="enter image description here"></a></p>
  
  <p>I try to create a function to lookup all the reason value of the id, however the results is unsuccessful which i get <strong>#Name</strong> as result.</p>
  
  <p>This is what I have so far.</p>
  
  <pre><code>Public Function Lookup_Concat(Search_ID As Range, Search_col As Range, Concat_del As String, Return_val_col As Range)
  Dim i As Long, j As Long
  Dim result As String
  For i = 1 To Search_Col.Count
          For Each Value In Search_ID
              If Search_Col.Cells(i, 1) = Value Then
                  result = result &amp; Return_val_col.Cells(i, 1).Value &amp; Concat_del
              End If
          Next Value
      Next i
      Lookup_Concat = Left(result, Len(result) - Len(Concat_del))
  
  End Function
  
  
  
  Sub Get_Reason()
  
  WorkSheets("20Apr'20").Activate
  Range("C:C" &amp; lastrow).FormulaR1C1 = "=Lookup_Concat(=Lookup_concat(RC[-1]:R[4]C[-1],Hold!R[-1]C[-1]:R[6]C,"","",Hold!R[-1]C:R[6]C)"
  
  End Sub
  </code></pre>
  
  <p>This is what I get:</p>
  
  <p><a href="https://i.stack.imgur.com/R6WbS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/R6WbS.png" alt="enter image description here"></a></p>
  
  <p>Really appreciate if someone can help me on this.</p>