Single Formula to acheive FIFO Costing

[jim may]

The left table (Columns A:E ) is my recent Purchase History of a product. Next, Columns G and H are my day-end Inventory Qtys. I'd like a formula in Column I that
would begin by searching the History table, in the example shown since I want the value as of 5/31/2015, I should first select (from the history table) the first date = to, or less than 5/31/2015, and in this case the 5/28/2015 row. And keep going up accumulating the full extended values, BUT no more that the Qty on Hand. In the # 5 layer I only need 586 of the 600 bought. I'm at a loss as to how to beging this formula. Can anyone assist? TIA, Jim

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2										From Layer/Series # >>	5	4	3	2	1
3	Layer or	** PURCHASING HISTORY **					** DAY END ON HAND INVENTORY **			Qty in Layer/Series >>	500	500	600	400	586	<< Limit total to 2,586
4	Series #	Date	Qty	Extension	U/Price		Date	Qty	Value	U/Price Layer/Series >>	$ 4.10	$ 3.90	$ 3.90	$ 3.85	$ 3.90
5	1	05/10/2015	600	2,340.00	$ 3.9000		05/31/2015	2,586	$ 10,165.40	<< Need Formula to Produce	2050	1950	2340	1540	2285.4	$ 10,165.40
6	2	05/15/2015	400	1,540.00	$ 3.8500		06/01/2015	2,398		which I have computed here >>
7	3	05/20/2015	600	2,340.00	$ 3.9000		06/02/2015	2,398
8	4	05/25/2015	500	1,950.00	$ 3.9000		06/03/2015	2,236
9	5	05/28/2015	500	2,050.00	$ 4.1000		06/04/2015	2,686
10	6	06/04/2015	500	1,936.97	$ 3.8739		06/05/2015	2,560
11	7	06/07/2015	450	1,777.12	$ 3.9492		06/06/2015	2,530
12	8	06/09/2015	30	130.00	$ 4.3333		06/07/2015	2,860
13	9	06/14/2015	320	1,643.07	$ 5.1346		06/08/2015	2,700
14	10	06/18/2015	260	950.93	$ 3.6574		06/09/2015	2,531
Sheet1

Cell Formulas
Range	Formula
I5	=SUM(K5:O5)
K3	=C9
K4	=E9
K5	=K3*K4
L3	=C8
L4	=E8
L5	=L3*L4
M3	=C7
M4	=E7
M5	=M3*M4
N3	=C6
N4	=E6
N5	=N3*N4
O3	=H5-SUM(K3:N3)
O4	=E5
O5	=O3*O4
P5	=SUM(K5:O5)

 

[sheetspread]

without usage of Arrays

Yes I should have noticed you already had the product in column D. Nice work.