Long Array issue with VBA

[snoh8r]

It was one thing to write this formula and have success but getting it to VBA has been killing me. I Couldn't use Vlookup to return items as one lookup value may have several entries and several results on the opposing sheet. So I concocted the formula below. Then I tried to put it into my maco, and that is when the headache began. I did some research about splitting the code. I can get this to work, except if I put an "=" in front of the first formula line. Then I get the unable to define array (or whatever). I started digging into the idea that each little piece needs to be an actual formula on it's own. I got nowhere fast. Maybe I'm not even going the right way with this. Maybe my formula could be better (I don't see how), but I could really use some help.

Thanks!

Sub THisDoesNotWork() ' not my original title but ....
Dim FormulaP1 As String, FormulaP2 As String, FormulaP3 As String, FormulaP4 As String, FormulaP5 As String, _
FormulaP6 As String, FormulaP7 As String, FormulaP8 As String, FormulaP9 As String, FormulaP10 As String, _
FormulaP11 As String, FormulaP12 As String, FormulaP13 As String, FormulaP14 As String, FormulaP15 As String, _
FormulaP16 As String, FormulaP17 As String, FormulaP18 As String, FormulaP19 As String, FormulaP20 As String, _
FormulaP21 As String, FormulaP22 As String, FormulaP23 As String, FormulaP24 As String, FormulaP25 As String, _
FormulaP26 As String, FormulaP27 As String, FormulaP28 As String, FormulaP29 As String, FormulaP30 As String, _
FormulaP31 As String, FormulaP32 As String



FormulaP1 = "IF(COUNTIF(Prod!A:A,A2)=1,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),IF(COUNTIF(Prod!A:A,A2)=2,IF(ISERROR(INDEX(Prod!$A:$B,SMALL" & "P2"

FormulaP2 = _
"(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2),IF(COUNTIF(Prod!A:A,A2)=3,IF(ISERROR&" & "P3"

FormulaP3 = _
"(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX" & "P4"

FormulaP4 = _
"(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2),IF(COUNTIF(Prod!A:A,A2)=4,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:" & "P5"

FormulaP5 = _
"$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:" & "P6"

FormulaP6 = _
"$4)),2),IF(COUNTIF(Prod!A:A,A2)=5,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2," & "P7"

FormulaP7 = _
"ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2," & "P8"

FormulaP8 = _
"ROW(Prod!$A:$B)),ROW($5:$5)),2),IF(COUNTIF(Prod!A:A,A2)=6,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B," & "P9"

FormulaP9 = _
"SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B," & "P10"

FormulaP10 = _
"SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($5:$5)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($6:$6)),2),IF(COUNTIF(Prod!A:A,A2)=7,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2" & "P11"

FormulaP11 = _
")),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR" & "P12"

FormulaP12 = _
"(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($5:$5)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($6:$6)),2)&CHAR" & "P13"

FormulaP13 = _
"(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($7:$7)),2),IF(COUNTIF(Prod!A:A,A2)=8,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B" & "P14"

FormulaP14 = _
")),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B" & "P15"

FormulaP15 = _
")),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($5:$5)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($6:$6)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B))" & "P16"

FormulaP16 = _
",ROW($7:$7)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($8:$8)),2),IF(COUNTIF(Prod!A:A,A2)=9,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$" & "P17"

FormulaP17 = _
"A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod" & "P18"

FormulaP18 = _
"!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($5:$5)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($6:$6)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(" & "P19"

FormulaP19 = _
"Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($7:$7)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($8:$8)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($9:$9)),2),IF(COUNTIF(Prod!A:A,A2)=10,IF(ISERROR" & "P20"

FormulaP20 = _
"(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&" & "P21"

FormulaP21 = _
"INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($5:$5)),2)&CHAR(10)" & "P22"

FormulaP22 = _
"&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($6:$6)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($7:$7)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($8:$8)),2)&CHAR(10)" & "P23"

FormulaP23 = _
"&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($9:$9)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($10:$10)),2),IF(COUNTIF(Prod!A:A,A2)=11,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$" & "P24"

FormulaP24 = _
"A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B" & "P25"

FormulaP25 = _
")),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($5:$5)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B" & "P26"

FormulaP26 = _
")),ROW($6:$6)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($7:$7)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($8:$8)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B" & "P27"

FormulaP27 = _
")),ROW($9:$9)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($10:$10)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($11:$11)),2),IF(COUNTIF(Prod!A:A,A2)=12,IF(ISERROR(INDEX(Prod!$A:$B,SMALL" & "P28"

FormulaP28 = _
"(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL" & "P29"

FormulaP29 = _
"(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($5:$5)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(" & "P30"

FormulaP30 = _
"IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($6:$6)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($7:$7)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($8:$8)),2)&CHAR(10)&INDEX(Prod!$A:$B," & "P31"

FormulaP31 = _
"SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($9:$9)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($10:$10)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($11:$11)),2)&CHAR(10)&INDEX" & "P32"

FormulaP32 = _
"(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($12:$12)),2),""""))))))))))))"

With ActiveSheet.Range("E2")
    
    .FormulaArray = FormulaP1
    .Replace "P2", FormulaP2
    .Replace "P3", FormulaP3
    .Replace "P4", FormulaP4
    .Replace "P5", FormulaP5
    .Replace "P6", FormulaP6
    .Replace "P7", FormulaP7
    .Replace "P8", FormulaP8
    .Replace "P9", FormulaP9
    .Replace "P10", FormulaP10
    .Replace "P11", FormulaP11
    .Replace "P12", FormulaP12
    .Replace "P13", FormulaP13
    .Replace "P14", FormulaP14
    .Replace "P15", FormulaP15
    .Replace "P16", FormulaP16
    .Replace "P17", FormulaP17
    .Replace "P18", FormulaP18
    .Replace "P19", FormulaP19
    .Replace "P20", FormulaP20
    .Replace "P21", FormulaP21
    .Replace "P22", FormulaP22
    .Replace "P23", FormulaP23
    .Replace "P24", FormulaP24
    .Replace "P25", FormulaP25
    .Replace "P26", FormulaP26
    .Replace "P27", FormulaP27
    .Replace "P28", FormulaP28
    .Replace "P29", FormulaP29
    .Replace "P30", FormulaP30
    .Replace "P31", FormulaP31
    .Replace "P32", FormulaP32
End With

End Sub

 

[snoh8r]

I finally found a way to make this work. I realized that each "Index" was itself a formula. From there I built this:
(Tested on Cell A142 as it has 12 returned values. Also tested all others)

Sub ThisWorks()
Dim iVal As Integer
iVal = Application.WorksheetFunction.CountIf(Sheets("Prod").Range("A:A"), Sheets("7-9-18").Range("A142"))
AAAA = "ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)"
AAA = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($1:$1)),2)"
BBB = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($2:$2)),2)"
CCC = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($3:$3)),2)"
 
DDD = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($4:$4)),2)"
EEE = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($5:$5)),2)"
FFF = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($6:$6)),2)"
GGG = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($7:$7)),2)"
HHH = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($8:$8)),2)"
III = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($9:$9)),2)"
JJJ = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($10:$10)),2)"
LLL = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($11:$11)),2)"
MMM = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($12:$12)),2)"
NNN = "&Char(10)&"
    If iVal = 1 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")"
    ElseIf iVal = 2 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB"
    ElseIf iVal = 3 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC"
    ElseIf iVal = 4 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD"
    ElseIf iVal = 5 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE"
    ElseIf iVal = 6 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF"
    ElseIf iVal = 7 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG"
    ElseIf iVal = 8 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH"
    ElseIf iVal = 9 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III"
    ElseIf iVal = 10 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III" & "&Char(10)&" & "JJJ"
    ElseIf iVal = 11 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III" & "&Char(10)&" & "JJJ" & "&Char(10)&" & "LLL"
    ElseIf iVal = 12 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III" & "&Char(10)&" & "JJJ" & "&Char(10)&" & "LLL" & "&Char(10)&" & "MMM"
    End If
    
    MsgBox iVal
    With Range("E142")
        .FormulaArray = Ret1
        .Replace "AAA", AAA
        .Replace "BBB", BBB
        .Replace "CCC", CCC
        .Replace "DDD", DDD
        .Replace "EEE", EEE
        .Replace "FFF", FFF
        .Replace "GGG", GGG
        .Replace "HHH", HHH
        .Replace "III", III
        .Replace "JJJ", JJJ
        .Replace "LLL", LLL
        .Replace "MMM", MMM
    End With

 

[snoh8r]

This was removed from the final copy.

NNN = "&Char(10)&"

 

[snoh8r]

And finally, I realized I had to account for change of cell A2 to A3, then A4 and so on. I came up with this:

Sub ProductCIArrays()
Dim rngAddress1 As Range, rngAddress5 As Range, iVal As Integer
Application.ScreenUpdating = False
Set rngAddress1 = Sheets(2).Range("A1:ZZ1").Find("PBI ID")
Set rngAddress5 = Sheets(2).Range("A1:ZZ1").Find("Product CI")
rngAddress1.Offset(1, 0).Select
i = 1
    
    Do While Not ActiveCell = ""
    
    AAAA = "ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A" & 1 + i & ",ROW(Prod!$A:$B)"
    
    AAA = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($1:$1)),2)"
    
    BBB = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($2:$2)),2)"
    
    CCC = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($3:$3)),2)"
     
    DDD = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($4:$4)),2)"
    
    EEE = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($5:$5)),2)"
    
    FFF = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($6:$6)),2)"
    
    GGG = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($7:$7)),2)"
    
    HHH = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($8:$8)),2)"
    
    III = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($9:$9)),2)"
    
    JJJ = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($10:$10)),2)"
    
    LLL = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($11:$11)),2)"
    
    MMM = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($12:$12)),2)"
    
    NNN = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($13:$13)),2)"
    
    iVal = Application.WorksheetFunction.CountIf(Sheets("Prod").Range("A:A"), ActiveCell)
    
        If iVal = 0 Then
            ret1 = ""
        ElseIf iVal = 1 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")"
        ElseIf iVal = 2 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB"
        ElseIf iVal = 3 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC"
        ElseIf iVal = 4 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD"
        ElseIf iVal = 5 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE"
        ElseIf iVal = 6 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF"
        ElseIf iVal = 7 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG"
        ElseIf iVal = 8 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH"
        ElseIf iVal = 9 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III"
        ElseIf iVal = 10 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III" & "&Char(10)&" & "JJJ"
        ElseIf iVal = 11 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III" & "&Char(10)&" & "JJJ" & "&Char(10)&" & "LLL"
        ElseIf iVal = 12 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III" & "&Char(10)&" & "JJJ" & "&Char(10)&" & "LLL" & "&Char(10)&" & "MMM"
        ElseIf iVal = 13 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III" & "&Char(10)&" & "JJJ" & "&Char(10)&" & "LLL" & "&Char(10)&" & "MMM" & "&Char(10)&" & "NNN"
        End If
        
        With rngAddress5.Offset(i, 0)
            .FormulaArray = ret1
            .Replace "AAA", AAA
            .Replace "BBB", BBB
            .Replace "CCC", CCC
            .Replace "DDD", DDD
            .Replace "EEE", EEE
            .Replace "FFF", FFF
            .Replace "GGG", GGG
            .Replace "HHH", HHH
            .Replace "III", III
            .Replace "JJJ", JJJ
            .Replace "LLL", LLL
            .Replace "MMM", MMM
            .Replace "NNN", NNN
            End With
        
        rngAddress1.Offset(1 + i, 0).Activate
        
        If ActiveCell = "" Then
            i = 1
        Else
            i = i + 1
        End If
        
    Loop
        
        Columns("E:E").Select
    
        With Selection
            .VerticalAlignment = xlBottom
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        
Application.ScreenUpdating = True
        
End Sub

This evaluates 580 rows and takes about 8 minutes. I would like to get it faster. If anyone is following this thread and has any ideas, I would really appreciate it.