Excel Formula - MAX, INDEX - !! Help Needed!!

[sean39]

Hi Experts!!, Pls. Help me.. I've followed a lot of exmaples here and tried different combination of MAX, IF & INDEX functions..even trying to nest MAX inside of Index but it doesnt work. Im frustrated and I cant seem to get a reliable formula to work consistently. I've attached my sample XLS which has essentially 2 active sheets- its in the link below

https://drive.google.com/file/d/0B5iyWwANed1BcldwT2dfQ2FsMlE/view?usp=sharing


What I need to build at work is:


In Sheet Port Watch, cell D3 thru D500, I'd like it to give latest(most recent by date) BUY price of Stock in cell A3 thru A500 as referenced in Sheet "Past Trades".


Similary In Sheet Port Watch, cell E3 thru E500, I'd like it to give latest(most recent by date) SOLD price of Stock in cell A3 thru A500 as referenced in Sheet "Past Trades".


In Sheet Port Watch, cell F3 thru F500, it should calculate NET remaining shares by adding and substracting bought and sold (in Sheet past trades) of stock in cell A3.


In Sheet Port Watch, cell G3 thru G500, it should calculate NET cost of remaining shares (referenced in Sheet Past Trades) of stock in cell A3.


The past trade sheet may grow fairly big like 10K lines..once all data is brought over. SO any efficient formulae like Index match may be suited for performance.
BUY & SELL is referenced in Sheet "Past Trades" in Column H marked Trade and also column D (any + qty is BUY and _ive is SOLD)


This is urgently required...Pls. help ASAP. thanks,

 

[sean39]

UPDATED FILE!! - below
https://drive.google.com/file/d/0B5i...ew?usp=sharing

Formula only needed in 4 yellow boxes...as marked on sheet. I've manually filled the right values in sheet.

In Sheet Port Watch, cell D3 thru D500, I'd like it to give latest(most recent by date) BUY price of Stock in cell A3 thru A500 as referenced in Sheet "Past Trades". In Cells E3...E500, it shd. give date of this trade.

Similary In Sheet Port Watch, cell F3 thru F500, I'd like it to give latest(most recent by date) SOLD price of Stock in cell A3 thru A500 as referenced in Sheet "Past Trades". In Cells G3...G500, it shd. give date of this trade.

In Sheet Port Watch, cell H3 thru H500, it should calculate NET remaining shares by adding and substracting bought and sold (in Sheet past trades) of stock in cell A3. I've already created a formula that works. Unless someone has a better, more stable options to suggest.

In Sheet Port Watch, cell I3 thru I500, it should calculate NET cost of remaining shares (referenced in Sheet Past Trades) of stock in cell A3. I've already created a formula that works. Unless someone has a better, more stable options to suggest.

Past trade sheet may grow fairly big like 10K lines..once all data is brought over. So any efficient formulae like Index match may be suited for performance.
BUY & SELL is referenced in Sheet "Past Trades" in Column H marked Trade and also column D (any + qty is BUY and _ive is SOLD)

This is urgently required...Pls. help. thanks,