Excel Smart Price Buckets "Pivot" or VB Assistance

[Adler212]

Hello eveyone,

Im looking for asssitance on calculating a buket level and total buckets, based on a comparison to another sheet ( Red culms)

In order to emphesize here is an example;


Sheet A ( Spend)

Date	PO Number	Vendor	Vendor ID	Item Desc	Item	Main Category	Sec Category	Currency	Unit Price	Quantity	Total Value	Site	Year	Bucket	Total Buckets
01/01/2016	1111111	Vendor A	10891	Yellow Package	1000015	Package	Flexible	EUR	30	10	300	Site A	2016	2	6
02/02/2016	1111112	Vendor A	10891	Yellow Package	1000015	Package	Flexible	EUR	10	200	2,000	Site A	2016	6	6
03/03/2016	1111113	Vendor B	10892	Yellow Package	1000064	Package	Plastic	EUR	38.55	500	19,275	Site A	2016	4	4

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

Sheet B ( Agreements):

Item	Vendor	Vendor Description	Agreement	Currency	Quantity	Unit Price	Bucket
1000015	10891	Vendor A	1005762	EUR	5	41.3	1
1000015	10891	Vendor A	1005762	EUR	10	30	2
1000015	10891	Vendor A	1005762	EUR	20	25	3
1000015	10891	Vendor A	1005762	EUR	30	20	4
1000015	10891	Vendor A	1005762	EUR	40	15	5
1000015	10891	Vendor A	1005762	EUR	50	10	6
1000064	10892	Vendor B	4000085	USD	10	55	1
1000064	10892	Vendor B	4000085	USD	100	30.74	2
1000064	10892	Vendor B	4000085	USD	200	24.48	3
1000064	10892	Vendor B	4000085	USD	300	38.55	4

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

Im looking for a way to calculate in the spend Sheet the buckets that appear in the Agreements Sheet. Somehow it's a Vlookup within a Vlookup but i cannot figure out how to properly design the formula so it will be based on the Item ID, and then calculate how many buckets does this crossed Item & Vendor include in the Agreement sheet, and on what bucket does it fall on ( per line).

I would appreciate any assistance on the matter,
Thanks in advance,
Asaf.