Dynamic Lookup

Any help is much appreciated as I have yet to figure out a combo of formulas to achieve my intent. Please review my spreadsheet for an overview. My intent is to produce a spreasheet as follows:

1. I am taking actual end of month values for a portfolio of stock, both dollar value and % weight in the portfolio. Let's use August as end of month for this example
2. A trade is then listed in the register that occurs in Sept.
3. I'd then like the hypothetical portfolio updated to show the portfolio estimate as end of september.
4. If no trade is registered, then the values in the hypothetical end of september section will remain the same as the current allocation.

I can't figure out the lookup and if combos that may be necessary. Essentially we are seeing first if the stock is listed in the trade register. If not, then the Sept value will remain. If it is listed in the trade register, then we must determine if it is a sell or buy to adjust the values. for simplicity's sake, let's assume only one trade on each stock will be entered in the register.

Is this possible?? Maybe some other table format is necessary? Thank you and I appreciate any help.


NathanPortfolio Test Template.xls

That is moving in the right direction. It still doesn't calculate correctly...see the attachment for example. I currently have 10mm SPY and register an order to sell 10mm. The target cell (hypothetical allocation) then reads 10mm with this suggested formula, yet it should read zero since all has been soldPortfolio Test Template.xlsAttachment 181249.

I thought to register the trade as a negative number, yet it then says my position is (-10) not zero. Any tweaks I can make to the formula?

You know what, I think I've figured it out and just enter buys as positive and sells as negative numbers. Thanks for the hint on iserror. That was it

=IF(ISERROR(MATCH(A4,$A$14:$A$29,0)),B4,B4+(INDEX($A$14:$C$29,MATCH(A4,$A$14:$A$29,0),3)))

@ nathanhamilton82

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as an infrequent user of the forum, you may have forgotten (or may not be aware) that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.