Lookup based on multiple criteria

paulkc

Board Regular
Joined
May 18, 2007
Messages
220
Office Version
  1. 365
First of all, I want to explain that I have a love/hate relationship with spreadsheets. It seems that every one I build ends up getting more and more complex because I love making them do more things that simplify my life. But at the same time, I think I sometimes spend so much time trying to make those adjustments that I never really save any time. But, either way, here we are again...

I've got a workbook that I use to keep up with investments in multiple accounts. I'm sharing mini sheets with sample data that I hope will make it easy to understand what I'm trying to accomplish. I keep a master portfolio that is the same for every account. But sometimes due to the differing times of starting those accounts, they have different holdings. BUT, they all have the same potential companies to buy into. I used to use different pages for each account but found that it was hard to keep the primary list of companies and it's accompanying data the same across all worksheets. So, I'm trying to combine them into a single page and use custom views to show hide the various columns for each account.

My current dilemma is that I want to keep all dividends on a separate worksheet and have them inserted into the main page by inserting a sum of the data that pertains to that particular account (notated in the very top row of the page) and the stock (as denoted in the 3rd column of the page).

So, if I'm looking at the ROTH account on the 'Portfolio' worksheet (again noted in the very top row of the sheet), I want to sum all dividends that match that account on the 'Dividends' worksheet. For example 'Portolio'!AG5 should populate with the SUM of ALL dividends that are listed in column D (because AG5 is in the LPL row) of the 'Dividends' worksheet that have the same account (in this case ROTH) listed in column B. If we were looking at AG6 on the 'Portolio' then it would move to column E on the 'Dividends' worksheet. I hope I'm making sense what I'm trying to accomplish.

Investment Workbook Copy.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARAS
1MASTER PORTFOLIOROTHINVEST
2Tranch 1Tranch 2Tranch 3Tranch 4Tranch 1
3Portfolio #STOCK IDSTOCK NAMECURRENT $Entry DateLI BUY PRICE% BELOWAVG DWN 1AVG DWN 2EXIT DATESOLDBuy Date Buy Price # Shares Commission Fees P/LBuy Date Buy Price # Shares Commission Fees P/LBuy Date Buy Price # Shares Commission Fees P/LBuy Date Buy Price # Shares Commission Fees P/L Div Income Total Invested 6109.9825 Portfolio PercentageTotal P/L 7053.29%Sell DateSell Price# SharesBuy Date Buy Price # Shares Commission Fees P/L
40.5iShares:MSCI Turkey (XNAS:TUR) - (2013-12-18)iShares:MSCI Turkey (XNAS:TUR)$ 41.562013-12-1852.400.2069n         
51LG Display Co., Ltd. (XNYS:LPL) - (2018-03-01)LG Display Co., Ltd. (XNYS:LPL)$ 3.482018-03-0116.000.782511.008.00n2023-04-045.9125-41.11%2023-04-045.9125-41.11%2023-04-045.9125-41.11%2023-04-045.9126-41.12%2.03596.879.77%-40.77%1012023-04-045.9125-41.11%
62CANON INC. (OTCM:CAJPY) - (2018-07-31)CANON INC. (OTCM:CAJPY)$ 28.002018-07-3134.000.176528.0022.00n2023-05-2424.7156.957.28%2023-05-2424.71513.31%2023-05-2424.71613.31% 14.77395.366.47%15.03%162023-05-2424.7156.957.28%
73THE KRAFT HEINZ COMPANY (XNAS:KHC) - (2018-11-01)THE KRAFT HEINZ COMPANY (XNAS:KHC)$ 32.542018-11-0160.000.457744.0035.00n2023-04-0538.743-16.00%2023-04-0538.743-16.00%2023-04-0538.744-16.00% 16.00387.406.34%-11.87%102023-04-0538.743-16.00%
84Invesco:Japanese Yen (ARCX:FXY) - (2020-07-30)Invesco:Japanese Yen (ARCX:FXY)$ 58.922020-07-3093.000.366580.0070.00n2023-04-0571.101-17.13%2023-04-0571.102-17.13%2023-04-0571.102-17.13% 355.505.82%-17.13%52023-04-0571.101-17.13%
95VanEck:Gold Miners (ARCX:GDX) - (2020-12-01)VanEck:Gold Miners (ARCX:GDX)$ 33.152020-12-0136.000.079225.0019.00n2023-09-0129.50312.37%   27.6888.501.45%43.65%32023-09-0129.50312.37%
106iShares:MSCI Brazil (ARCX:EWZ) - (2021-02-25)iShares:MSCI Brazil (ARCX:EWZ)$ 27.292021-02-2536.000.241929.0022.00n2023-04-0527.344-0.18%2023-04-0527.345-0.18%2023-04-0527.345-0.18% -382.766.26%-0.18%142023-04-0527.344-0.18%
117Glbl X Silver Miners ETF (ARCX:SIL) - (2021-03-25)Glbl X Silver Miners ETF (ARCX:SIL)$ 31.242021-03-2543.000.273533.0023.00n2023-04-0531.924-2.13%2023-04-0531.924-2.13%2023-04-0531.924-2.13% 12.04383.046.27%1.01%122023-04-0531.924-2.13%
128Invesco:Euro (ARCX:FXE) - (2021-05-27)Invesco:Euro (ARCX:FXE)$ 99.172021-05-27116.000.1451111.00106.00n2023-04-05100.982-1.80%2023-04-05100.982-1.80%2023-04-05100.982-1.80% 1.50605.889.92%-1.55%62023-04-05100.982-1.80%
139KYNDRYL HOLDINGS, INC. (XNYS:KD) - (2021-11-04)KYNDRYL HOLDINGS, INC. (XNYS:KD)$ 25.112021-11-04n         
1410UBS Group Inc. (XNYS:UBS) - (2022-01-18)UBS Group Inc. (XNYS:UBS)$ 30.422022-01-18196.22n         
1511Nokia Oyj (XNYS:NOK) - (2022-01-18)Nokia Oyj (XNYS:NOK)$ 3.712022-01-186.500.42925.204.85n         
1612LLOYDS BANKING GROUP PLC (XNYS:LYG) - (2022-03-01)LLOYDS BANKING GROUP PLC (XNYS:LYG)$ 2.712022-03-012.70(0.0037)2.151.90n2023-04-054.8927-44.58%2023-04-054.8927-44.58%2023-04-054.8927-44.58% -396.096.48%-44.58%812023-04-054.8927-44.58%
17133M COMPANY (XNYS:MMM) - (2023-01-25)3M COMPANY (XNYS:MMM)$ 101.542023-01-251200.1538100.0075.00n2023-04-062.42414104.55%2023-04-062.42414104.55%  -198.033.24%#######822023-04-062.42414104.55%
1814iShares:iBoxx $IG Corp (ARCX:LQD) - (2023-02-27)iShares:iBoxx $IG Corp (ARCX:LQD)$ 108.372023-02-27108(0.0034)100.0090.00n2023-10-0487.94223.23%2023-10-0487.94223.23%  12.41351.765.76%26.76%42023-10-0487.94223.23%
1915VERIZON COMMUNICATIONS INC. (XNYS:VZ) - (2023-05-01)VERIZON COMMUNICATIONS INC. (XNYS:VZ)$ 39.782023-05-01410.029833.0028.00n2023-10-0499.721-60.11%2023-10-0499.722-60.11%  17.90299.164.90%-54.12%32023-10-0499.721-60.11%
2016Bayer AG (OTCM:BAYRY) - (2023-06-01)Bayer AG (OTCM:BAYRY)$ 7.372023-06-01150.508712.0010.00n2023-05-2436.255-79.67%2023-07-1732.346-77.21%  ########375.296.14%#######112023-05-2436.255-79.67%
2117Equinor ASA (XNYS:EQNR) - (2023-06-01)Equinor ASA (XNYS:EQNR)$ 27.382023-06-0128.000.022122.0016.00n2023-06-0113.87136.9590.08%2023-09-2712.00166.95120.20%2023-12-148.66246.95205.94% 580.159.50%141.45%532023-06-0113.87136.9590.08%
2218ArcelorMittal SA (XNYS:MT) - (2023-06-28)ArcelorMittal SA (XNYS:MT)$ 23.992023-06-2830.000.200321.0014.00n         
2319TESCO PLC (OTCM:TSCDY) - (2023-08-01)TESCO PLC (OTCM:TSCDY)$ 11.562023-08-0110.50(0.1010)8.607.50n2023-09-0526.804-56.87%   0.96107.201.75%-55.97%42023-09-0526.804-56.87%
2420iShares:20+ Trs Bd ETF (XNAS:TLT) - (2023-10-19)iShares:20+ Trs Bd ETF (XNAS:TLT)$ 93.882023-10-1986.00(0.0916)80.0067.00n2023-09-0110.08106.95771.28%   6.95100.801.65%777.73%102023-09-0110.08106.95771.28%
2521FRANKLIN RESOURCES, INC. (XNYS:BEN) - (1900-01-00)FRANKLIN RESOURCES, INC. (XNYS:BEN)$ 21.96n       
2622Schwab Value Advantage Money Fund;Investor - (1900-01-00)Schwab Value Advantage Money Fund;Investorn      
Portfolio
Cell Formulas
RangeFormula
AG5AG5=SUM(Dividends!J:J)
AG6AG6=SUM(Dividends!E:E)
AG7AG7=SUM(Dividends!F:F)
G4:G12,G15:G24G4=(F4-D4)/F4
K4,K6:K12,K14:K23K4=IF(J4="","n","y")
K5K5=IF(AND(J5="",E5=""),"",IF(J5="","n","y"))
AG9AG9=SUM(Dividends!I:I)
AG10AG10=SUM(Dividends!G:G)
AG11AG11=SUM(Dividends!P:P)
AG12AG12=SUM(Dividends!H:H)
AG16AG16=SUM(Dividends!L:L)
AG17AG17=SUM(Dividends!M:M)
AG18AG18=SUM(Dividends!K:K)
AG19AG19=SUM(Dividends!R:R)
AG20AG20=SUM(AY:AY)
AH3AH3="Total Invested"&CHAR(10)&SUM(AH4:AH403)
AH4:AH24AH4=IF(SUM(N4*O4+S4*T4+X4*Y4+AC4*AD4)=0,"",SUM(N4*O4+S4*T4+X4*Y4+AC4*AD4))
AG23AG23=SUM(BC:BC)
AG24AG24=SUM(BB:BB)
AM4:AM24AM4=IF(O4="","",SUM(O4+T4+Y4+AD4))
D4:D25D4=C4.[Price]
AI4:AI25AI4=IF(AH4="","",AH4/SUM(AH$4:AH$101))
B4:B26B4=IF(C4="","",VALUETOTEXT(C4,0) & " - (" & TEXT(E4,"yyyy-mm-dd") & ")")
Q4:Q26,AS4:AS26,AF4:AF26,AA4:AA26,V4:V26Q4=IF(OR(M4="",N4="",O4="",M4=0,N4=0,N4=0),"",(($D4*O4)-(N4*O4)-P4)/((N4*O4)+P4))
AJ3AJ3="Total P/L"&CHAR(10)&ROUND((AVERAGE(AJ4:AJ403))*100,2)&"%"
AJ4:AJ26AJ4=IF(Q4="","",(($D4*(O4+T4+Y4+AD4))-((N4*O4)+(S4*T4)+(X4*Y4)+(AC4*AD4)+P4+U4+Z4+AE4)+AG4)/((N4*O4)+(S4*T4)+(X4*Y4)+(AC4*AD4)+P4+U4+Z4+AE4))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B5:B1016Expression=$B$5=""textNO
F3:F1048576Expression=F3>D4textNO
H3:H1048576Expression=H3>D4textNO
I3:I1048576Expression=I3>D4textNO


Investment Workbook Copy.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXY
1PortfolioTURLPLCAJPYKHCFXYGDXEWZSILFXEKDUBSNOKLYGMMMLQDVZBAYRYEQNRMTTSCDYTLTBENSCHWAB
22023-05-08ROTH0.71
32023-05-09ROTH2.68
42023-05-09ROTH(0.17)
52023-06-02ROTH6.50
62023-06-02ROTH(0.13)
72023-06-08ROTH0.79
82023-06-13ROTH10.59
92023-06-30ROTH4.00
102023-07-10ROTH0.38
112023-07-11ROTH0.84
122023-07-11ROTH2.02
132023-08-01INVEST3.26
142023-08-08INVEST0.95
152023-08-08INVEST2.65
162023-08-08INVEST(0.17)
172023-09-05INVEST7.65
182023-09-05INVEST-0.13
192023-09-11INVEST1.06
202023-09-22INVEST3.77
212023-09-22INVEST(0.08)
222023-09-29INVEST4.00
232023-10-10INVEST1.05
242023-11-01INVEST7.32
252023-11-07INVEST1.09
262023-11-07INVEST(0.05)
272023-11-07INVEST2.59
282023-11-07INVEST(0.78)
292023-11-07INVEST(0.12)
302023-11-08INVEST0.05
312023-11-08INVEST1.16
322023-11-08INVEST(0.05)
332023-11-09INVEST-1.98
342023-12-01INVEST1.45
352023-12-07INVEST0.88
362023-12-07INVEST-0.13
372023-12-07INVEST1.18
382023-12-08INVEST1.16
392023-12-11INVEST-0.35
402023-12-12INVEST6.00
412023-12-20INVEST1.18
422023-12-22INVEST1.50
432023-12-27INVEST17.09
442023-12-29INVEST4.00
452024-01-08INVEST1.65
462024-01-09INVEST1.21
472024-02-01INVEST7.32
482024-02-07INVEST1.17
492024-02-08INVEST1.19
502024-02-13INVEST2.62
512024-02-13INVEST(0.79)
522024-02-13INVEST(0.17)
532024-03-07INVEST1.18
542024-03-08INVEST1.11
552024-03-12INVEST6.04
562024-04-01INVEST4.00
572024-04-05INVEST1.23
582024-04-05INVEST-0.13
592024-04-05INVEST7.38
602024-04-08INVEST1.18
Dividends
Cells with Data Validation
CellAllowCriteria
B2:D60List='Portfolio List'!$A:$A
 
You're welcome. Glad you figured it out. One last thing... I would lock $C4 so the stock name column doesn't move when you paste the formula into a different column.
 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
I'm at a lost as to why this stopped working. I know it was working when we worked on this back in June but at some point it stopped working.

Investment Workbook - Copy.xlsm
ACDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1MASTER PORTFOLIOSARAH - ROTH
2Tranch 1Tranch 2Tranch 3Tranch 4
3Portfolio #STOCK NAMESTOCK NAME TEXTCURRENT $Entry DateLI BUY PRICE% BELOWAVG DWN 1AVG DWN 2EXIT DATESOLDBuy Date Buy Price # Shares Commission Fees P/LBuy Date Buy Price # Shares Commission Fees P/LBuy Date Buy Price # Shares Commission Fees P/LBuy Date Buy Price # Shares Commission Fees P/L Div Income
41LG Display Co., Ltd. (XNYS:LPL)LPL$ 4.182018-03-0116.000.738811.008.00n2023-04-045.9125507.67%2023-04-045.9125507.67%2023-04-045.9125507.67%2023-04-045.9126507.61%0
53THE KRAFT HEINZ COMPANY (XNAS:KHC)KHC$ 35.912018-11-0160.000.401544.0035.00n2023-04-0538.74365.54%2023-04-0538.74365.54%2023-04-0538.74465.54% 0
64Invesco:Japanese Yen (ARCX:FXY)Invesco:Japanese Yen (ARCX:FXY)$ 64.132020-07-3093.000.310480.0070.00n2023-04-0571.101-44.67%2023-04-0571.102-44.67%2023-04-0571.102-44.67% -
75VanEck:Gold Miners (ARCX:GDX)VanEck:Gold Miners (ARCX:GDX)$ 39.342020-12-0136.00(0.0928)25.0019.00n2023-09-0129.5033.19%   -
86iShares:MSCI Brazil (ARCX:EWZ)iShares:MSCI Brazil (ARCX:EWZ)$ 30.442021-02-2536.000.154429.0022.00n2023-04-0527.34423.66%2023-04-0527.34523.66%2023-04-0527.34523.66% -
LI Portfolio
Cell Formulas
RangeFormula
AG4:AG8,AB4:AB8,W4:W8,R4:R8AG4=IF(OR(AC4="",AD4="",AE4="",AC4=0,AD4=0,AD4=0),"",(($E5*AE4)-(AD4*AE4)-AF4)/((AD4*AE4)+AF4))
AH4AH4=IFERROR(SUM(IF((N$1=Dividends!$B$2:$B$20000)*(Dividends!$C$1:$Y$1=TEXTBEFORE(TEXTAFTER(D4,":",-1),")")),Dividends!$C$2:$Y$60,0)),0)
AH5AH5=IFERROR(SUM(IF((N$1=Dividends!$B$2:$B$20000)*(Dividends!$C$1:$Y$1=TEXTBEFORE(TEXTAFTER($D5,":",-1),")")),Dividends!$C$2:$YY$20000,0)),0)
AH6:AH8AH6=IFERROR(SUM(IF((N$1=Dividends!$B$2:$B$20000)*(Dividends!$C$1:$Y$1=TEXTBEFORE(TEXTAFTER($C6,":",-1),")")),Dividends!$C$2:$YY$20000,0)),0)
E4:E8E4=C4.[Price]
H4:H8H4=(G4-E4)/G4
L4L4=IF(AND(K4="",F4=""),"",IF(K4="","n","y"))
L5:L8L5=IF(K5="","n","y")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G3:G1048576Expression=G3>E4textNO
I3:I1048576Expression=I3>E4textNO
J3:J1048576Expression=J3>E4textNO
 
Upvote 0

Forum statistics

Threads
1,224,271
Messages
6,177,618
Members
452,786
Latest member
k3calloway

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top