Need some help referencing a string in an Index formula array
My code below:
Sub Loop_Test2()
Dim i As Long
Dim j As Long
Dim CountAll As Long
Dim CountXL As Long
Dim CustomerName As String
ActiveSheet.Range("A1").Activate
CountAll = ActiveSheet.Range("A35")
For j = 1 To CountAll
i = 2
CountXL = Cells(i, j).Value
For i = 1 To CountXL
CustomerName = Cells(1, j).Value
'MsgBox CustomerName
Cells(i + 2, j).FormulaArray = "=IFERROR(INDEX(Sheet2!$A:$B,SMALL(IF(Sheet2!$A:$A=CustomerName,ROW(Sheet2!$A:$A)),ROW(1:1))*1,2),0)"
Next i
Next j
End Sub
There is no error; however i need This part fixed so it references the value instead of the actual word inside the formula:
IF(Sheet2!$A:$A=CustomerName