ПОИСКПОЗ(H1;$A:$A)

ПОИСКПОЗ(H1;$A:$A;)

Option Explicit
Option Base 1

Sub Rio_Split()

'Author:    Roman "Rioran" Voronov
'Date:      the 17-th of February, 2015
'Feedback:  voronov_rv@mail.ru

Dim ArrA 'Basic data
Dim ArrB 'Final data
Dim A As Long 'Rows number for ArrA
Dim B As Long 'Rows number for ArrB
Dim i As Long, j As Long, k As Long, q As Long

A = Cells(Rows.Count, 1).End(xlUp).Row - 1
ArrA = Range(Cells(2, 1), Cells(A + 1, 6))

With Application.WorksheetFunction
     B = .Sum(.Index(ArrA, 0, 6))
End With

ReDim ArrB(B, 4)

For i = 1 To A
     For j = 1 To ArrA(i, 6)
         q = q + 1
         For k = 1 To 3
             ArrB(q, k) = ArrA(i, k)
         Next k
         ArrB(q, 4) = ArrA(i, 4) - 1 + j
     Next j
Next i

Cells(2, 8).Resize(B, 4).Value = ArrB

End Sub

=ЕСЛИОШИБКА(ПРОСМОТР("яяя";ЕСЛИ(($V2=$A$2:$A$6)*(X$1>=$E$2:$E$6)*(X$1<=$F$2:$F$6);$C$2:$C$6));"")