FIFO stock info using search functions and averaging data

[JonnyHazell]

Hi Guys,

I am currently using pivot tables to get our current cost of good prices for thousands of products via thousands of lines of data, so as you could imagine its very time consuming. I've been trying to come up with a way to automate it, but I'm coming up with blanks.

I'd like to be able to enter a customer name and product, and to use some excel magic to return me the average COG of the latest 1000 units purchased.

Here is a very basic example

[TABLE="width: 438"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Product[/TD]
[TD]Invoiced[/TD]
[TD]Qty[/TD]
[TD]Amount[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Eggs[/TD]
[TD="align: right"]02/08/2015[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Sausage[/TD]
[TD="align: right"]02/08/2015[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]48[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Sausage[/TD]
[TD="align: right"]02/07/2015[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Sausage[/TD]
[TD="align: right"]02/06/2015[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Eggs[/TD]
[TD="align: right"]02/08/2015[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Eggs[/TD]
[TD="align: right"]02/07/2015[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wanted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer[/TD]
[TD]Product[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Sausage[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]5.8[/TD]
[/TR]
</tbody>[/TABLE]

So I'd like to enter Steve and Sausage, and for excel to return me the most recent average of the cost of 10 units.

Is this possible or just a dream I need to wake up from and get on the daily slog?

Thanks for any input!
Jonny