VBA dinamic vlookup table array based on if statement

[morjaman]

Hi there!


I will be grateful for some help.
I have WorkBook with 2 sheets in it.


sheet 1

name	macro / vlookup result	value1	value2
A	J 11 4 42621	40	40
A	J 11 4 42621	272	272
A	J 11 4 42621	319	319
A	J 11 4 42621	128	120
A	J 13 1 42651	224	224
A	J 13 1 42651	286	286
A	J 13 1 42651	399	175
A	J 13 2 42651	32	32
A	J 13 2 42651	200	152
B	J 28 3 42951	305	305
B	J 28 3 42951	496	496
B	J 28 3 42951	1999	1175
B	J 36 5 42978	496	496

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

sheet2

A	J 11 4 42621
A	J 13 1 42651
A	J 13 2 42651
A	J 21 2 42616
A	J 25 1 42622
A	J 25 1 42908
A	J 25 3 42908
B	J 28 3 42951
B	J 36 5 42978
C	J 38 1 42978
C	J 39 3 42985

<colgroup><col><col></colgroup><tbody>
</tbody>

My need is in the 1-st sheet in col.B macro to put formulas vlookup(A:A;sheet2!$A$1:$B$11;2;0)
until Value1 (col.C) > Value2 (col.D) (bolded) then to skip first row in table array vlookup(A:A;sheet2!$A$2:$B$11;2;0) and continue until value in Col.A <> ""

Any help will be appreciated.