Word permutations

Complexity =Sheet7!$D$2:$D$4
Lob            =Sheet7!$C$2:$C$5
Role           =Sheet7!$A$2:$A$4
Scope         =Sheet7!$B$2:$B$5

=IF(ROWS($F$2:F2)>ROWS(Role)*ROWS(Scope)*ROWS(Lob)*ROWS(Complexity),"",INDEX(Role,1+MOD(ROWS($F$2:F2)-1,ROWS(Role))))

=IF(F2="","",INDEX(Scope,1+MOD(INT((ROWS($G$2:G2)-1)/ROWS(Role)),ROWS(Scope))))

=IF(F2="","",INDEX(Lob,1+MOD(INT((ROWS($H$2:H2)-1)/ROWS(Role)/ROWS(Scope)),ROWS(Lob))))

=IF(F2="","",INDEX(Complexity,1+MOD(INT((ROWS($I$2:I2)-1)/ROWS(Role)/ROWS(Scope)/ROWS(Lob)),ROWS(Complexity))))

Sub test()
    Dim arrRole, arrScope, arrLob, arrComplexity, arrResults
    Dim a As Long, b As Long, c As Long, d As Long, Counter As Long

    arrRole = Array("Lead", "Support", "Advise")
    arrScope = Array("Global", "Multi-Country", "UK Regional", "Local")
    arrLob = Array("One", "Few", "Many", "All")
    arrComplexity = Array("Low", "Medium", "High")
    
    For a = LBound(arrRole) To UBound(arrRole)
        For b = LBound(arrScope) To UBound(arrScope)
            For c = LBound(arrLob) To UBound(arrLob)
                For d = LBound(arrComplexity) To UBound(arrComplexity)
                    Counter = Counter + 1
                    If Counter = 1 Then
                        ReDim arrResults(1 To 4, 1 To Counter) As String
                    Else
                        ReDim Preserve arrResults(1 To 4, 1 To Counter) As String
                    End If
                    arrResults(1, Counter) = arrRole(a)
                    arrResults(2, Counter) = arrScope(b)
                    arrResults(3, Counter) = arrLob(c)
                    arrResults(4, Counter) = arrComplexity(d)
                Next d
            Next c
        Next b
    Next a
    arrResults = Excel.Application.Transpose(arrResults)
    
    With ActiveWorkbook
        With .ActiveSheet
            .Cells(2, 1).Resize(Counter, 4) = arrResults
        End With
    End With
    Erase arrResults
End Sub