Option Explicit

Sub dakine55()    'коллекция в словаре
    Dim a, b, i&, t$, Dic As Object
    Dim el, x&

    With Sheets("Материалы")
        a = .Range("C3", .Cells(.Rows.Count, "A").End(xlUp)).Value
    End With

    Set Dic = CreateObject("Scripting.Dictionary")
    With Dic
        .CompareMode = 1
        For i = 1 To UBound(a)
            t = a(i, 3)
            If Not .exists(t) Then .Add t, New Collection
            .Item(t).Add i
        Next
    End With

    b = Sheets("Заказ").[a7].CurrentRegion.Value
    ReDim c(1 To UBound(a), 1 To 3)
    x = x + 1
    c(x, 1) = "Материалы"
    c(x, 2) = "кол-во"
    c(x, 3) = "Продукция"
    For i = 1 To UBound(b)
        t = b(i, 2)
        If Dic.exists(t) Then
            For Each el In Dic.Item(t)
                x = x + 1
                c(x, 1) = a(el, 1)
                c(x, 2) = a(el, 2) * b(i, 4)
                c(x, 3) = t
            Next
        End If
    Next

    With Workbooks.Add(1).Sheets(1).Cells(1).Resize(x, 3)
        .Value = c
        .Columns.AutoFit
    End With
End Sub