Summation of Values Based on Match or Substitute

[captainxcel]

Hi and thanks in advance! I'm trying to create what's called a re-balance spreadsheet and I'm stuck on the formulas to use in the yellow shaded cells in column V. There are 4 named tables in the sheet (from left to right)
Assets_and_Sub_Table
Model_Portfolios
Client_Allocations
Broker_Download

In the Assets_and_Sub_Table I specify alternative ETFs (i.e. stocks) which a client may hold to fulfill the model portfolio recommended holding. Clients get a combination of model portfolios based upon their risk tolerance and age.
The target for a client multiplicative. So, for example, the formula in cell T3 is

=INDEX(Client_Allocations,MATCH($S$1,Client_Allocations[Client],0),2)*INDEX(Model_Portfolios,MATCH(S3,Model_Portfolios[Ticker],0),2)+INDEX(Client_Allocations,MATCH($S$1,Client_Allocations[Client],0),3)*INDEX(Model_Portfolios,MATCH(S3,Model_Portfolios[Ticker],0),3)

So, for example, Jane, with her $100,000 portfolio should have $36,000 of the stock AGG. Per the Broker_download table she holds the proper equivalent amount of AGG since she came into the day with $14,000 of AGG and $22,000 of allowable substitute BND. Can someone help with the proper formula for adding the amount of a recommended holding plus the allowable substitutes a client has. Thanks.

 

[mrshl9898]

Like this?

=SUMIFS(Q:Q,O:O,S1,P:P,S3)+SUMIFS(Q:Q,O:O,S1,P:P,VLOOKUP(S3,B:D,3,0))+SUMIFS(Q:Q,O:O,S1,P:P,VLOOKUP(S3,B:D,4,0))

 

[captainxcel]

Trying this now.

 

[captainxcel]

Yes. This worked. Thanks. I changed the B:D to B:E and S1 to $S$1 for Jane (in order to copy down and $S$8 for Joe. I wasn't aware that VLOOKUP worked in this way. But it follows that if I add another column to the Asset_and_Subs_Table that I would add one more sumifs term.