Previous Occurences DISCUSSION

Hey everyone,

I'd love to hear some opinions regarding VALUE OF PREVIOUS OCCURENCES.
Let's take a FRUIT SHOP as an example.

	A	B	C	D	E	F
1	Date	Fruit	Buy	Sell	Previous Stock	Current Stock
2	8/1/13	Apple	50	0	0	50
3	8/1/13	Banana	50	0	0	50
4	8/1/13	Orange	50	0	0	50
5	8/2/13	Apple	0	10	50	40
6	8/3/13	Apple	0	5	40	35
7	8/4/13	Banana	0	10	50	40

We also assume that,

1. Every DATE and FRUIT pair is UNIQUE
2. List are sorted in ASCENDING order

What I'm trying to achieve here is to retrieve value of PREVIOUS STOCK of CURRENT ROW's FRUIT which is CURRENT STOCK of PREVIOUS OCCURENCE of CURRENT ROW's FRUIT.
The formula I'm currently using is,

E5 =LOOKUP(2,1/(B$2:B4=B5),F$2:F4)

Some questions I'd like to ask,

1. Is it possible to replace B$2:B4 and F$2:F4 with a NAMED RANGE?
2. Is there another formula which can perform better when we have thousands of row data?
3. How can we improve the formula so it can be used in cell E2, E3, and E4?

Thank you for reading! I'm looking forward to hear some feedbacks.

Regards,


Mustank