VLOOKUP - Check if Cell contains Zero. If it does move onto next..

[AndyGray]

Looking for quite an advanced formula in Excel using a Vlookup...

I have a list of customer prices in my RawData. These are layed out like this

DESC	REFERENCE	Cardcode	PRICE1	FROM1	TO1	PRICE2	FROM2	TO2	PRICE3	FROM3	TO3	PRICE4	FROM4	TO4
Mr Excel	Q733/070	C001000	8.48	0	0	0	0	0	0	0	0	0	0	0

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

Now price 1 is a standard price. Price2/3/4 are all price breaks. Which will show "Price2" 8.00, From2 100, To2 10000. So basically if a customer orders between 100 and 10000 he gets it at a discounted price.


I need it putting into a separate sheet which is formatted like so...

ItemCode	CardCode	LineNum	Amount	SPP1LNum	Price	SPP2LNum	Currency
QMB06	C001000	0	>100	0	3.45	0	GBP
QMB06	C001000	1	<=100	0	3.2	1	GBP
QMB11	C001000	0	>80	0	5.15	0	GBP
QMB11	C001000	1	<=80	0	4.95	1	GBP

<colgroup><col style="width:47pt" width="62" span="8"> </colgroup><tbody>
</tbody>

(This has been manually inserted) However, i Want to be able to say "If Price2 has a value in. Then apply "Price2" (from RawData" and insert it into "Price". If that makes sense... Then it need to look for the next row, create a new row, and insert "Price3" If there is another price break in there... If it doesn't have a price in Price 3. Then it needs to ignore that and move onto the next product? If that makes sense?

I will try and attach my spreadsheet...
The Data is in RawData and the SPP2 is where I am looking.

Please find spreadsheet on following link...

https://mbcladding.co.uk/agray2234.xlsx

Thanks.