I would Like to have excel scan a column, backwards in time for the most recent match of a

Screenshot (22).png


First time I have been in a forum and not very experienced with excel, so please bare with me.

I am trying to create a stock trading journal with calculations in it.


See picture of my spreadsheet, I removed many of the non relevant columns.


Currently I am using =IF(D6="Stock",H6/J6,G6*J6/E6) (in the stocks current cost basis columns (K) to give me the cost basis if its a purchase designated by the word stock, otherwise give me the per share cost or profit earned by the trade.

There can be multiple columns for each trade/symbol, being more then one lot of stocks included in the trade that are currently open.

I'd like to have the spreadsheet do is,
when I enter stock symbol it looks back to match up the last trade for each lot in the trade (this might be more then one row or previous trade as the lots may not always be part of the same trade), (maybe by matching the stock symbol, matching purchase date and lot number) and then keep a running total, adjusting the last matching lot number's cost basis by the net premium per share (Column G) and putting that/those total(s) in the most recent row (Stocks Current Cost Basis Lot) or the column next to it if another column is needed to do the calculation... row by row trade by trade.

Can this be done? How?

Please ask me any question if I was not exact or clear.

Thanks,

Rick


Screenshot (22).png