Valuation of Closing Stock FIFO (using purchase history and closing stock qty only)

[Ahmed23236]

I would like to value closing stock of an item based on latest prices the item bought on.
Table 1: Stock account

CODE	Details	Units In	Stock balanceQTY	VALUE
2602​	Red Bull Blue	Can	31
1014​	Coke Tin Mini_150ML	Can	57
407236​	Mars chocolate Single 51gm	PKT	12
461122​	Sinkers chocolate Twin 50g	PKT	31

Table 2; Purchases

DATE	CODE	ITEM NAME	UNIT	QTY	COST Price Rupees	TOTAL
22-Sep​	2602​	Red Bull Blue	Can	24​	35​	840​
22-Sep​	1014​	Coke Tin Mini_150ML	Can	30​	6​	180​
22-Sep​	407236​	Mars chocolate Single 51gm	PKT	24​	7​	168​
22-Sep​	461122​	Sinkers chocolate Twin 50g	PKT	24​	8​	192​
29-Sep​	1014​	Coke Tin Mini_150ML	Can	30​	8​	240​
21-Oct​	2602​	Red Bull Blue	Can	24​	32​	768​
21-Oct​	461122​	Sinkers chocolate Twin 50g	PKT	24​	9​	216​

For example: Closing stock of Redbull is 31 tin. Latest price bought is Rupees 32 but qty is less than closing stock . So Stock value is 24*32rupees+(31-24)*35rupees= 1013 rupees
I used this formula to get latest price =LOOKUP(2,1/(PURCHASE!$D$6:$D$9999='STOCK ACCOUNT'!$A3),PURCHASE!$I$6:$I$9999) and multiplied with closing qty but if latest qty bought is less than balance qty i can not use this formula.

Please suggest a formula or a VBA.