Index Match with Multiple Criteria including most recent date/last day of the month

[Dudetrek]

I'm an Excel 365 user, and a very new one at that. Forgive me for my lack of etiquette within this forum, I hope that this first post may help introduce me to a way to solve this specific problem of mine, as well as to learn more about Excel 365 in general, and how to properly interact with this forum community. Thanks for any and all guidance and advice

The 5x10 table is in a sheet called "Transactions" and the 2x4 Table is in a sheet called "TotalsMonthly". Let's assume today's date is still in February of 2022. I'm trying to create a function to place into cell C2 of "TotalsMonthly" so that I can fill the rest of the C Column (for January, which is already over) and B Column (for February, which hasn't ended yet) with running account totals. I've tried INDEX/MATCH functions like this one below:

=INDEX(Transactions!$A:$E,MATCH(TotalsMonthly!$A2,(Transactions!$A:$A>=TotalsMonthly!C$1)*(Transactions!$A:$A<TotalsMonthly!B$1)),5)

But I keep getting either "You can't change part of an array" notices, and/or "N/A" inside the C2 of "TotalsMonthly".

Hope I'm getting my message across clearly enough to interpret, and if not, I am happy to clarify to the best of my abilities. Obviously, these tables are an equivalent of the excel tables I'm trying to work with, but the real tables contain my actual financial information, which I'd rather not disclose.


"Transactions"

Date	Merchant	Amount	Account	Account Total
2/21/2022	Paycheck	250	Chase Checking	1350
2/16/2022	Amazon	-50	Chase Checking	1100
2/6/2022	Transfer	120	Chase Savings	2120
2/6/2022	Transfer	-120	Cash	94.5
2/6/2022	Server Tips	120	Cash	214.5
1/28/2022	Paycheck	250	Chase Checking	1150
1/12/2022	Mcdonalds	-5.50	Cash	94.5
1/1/2022	Cash Withdrawal	100	Cash	100
1/1/2022	Cash Withdrawal	-100	Chase Checking	900

"TotalsMonthly"

Account	2/1/2022	1/1/2022
Cash	?	?
Chase Checking	?	?
Chase Savings	?	?

Thanks so much for your time, and I will continue plowing through the forums looking for more learning opportunities while this post makes its way across the forum. Happy spreadsheeting!

-Dudetrek

 

[Phuoc]

Try this:

=INDEX(Transactions!$E:$E,MATCH(1,(TotalsMonthly!$A2=Transactions!$D:$D)*(Transactions!$A:$A>=TotalsMonthly!C$1)*(Transactions!$A:$A<TotalsMonthly!B$1),0))

.

 

[Peter_SSs]

Welcome to the MrExcel board!

If you still need help with this, please manually complete the expected results in 'TotalsMonthly' using the sample data shown in post #1 , post that sheet again and explain how you got those results manually.

BTW, for the future I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

 

[Dudetrek]

Phuoc- that Index/Match formula spit out "N/A" for me again. :/

Peter_SSs- I got onboard with XL2BB and below is the exact data from my sheets. The first sheet is "Transactions", the second sheet is "Totals Monthly" with the manually inputted results I expect to get.

• For example, I got Totals Monthly B91 by referring to Transactions H2 (the most recent day from the month of June with "Cash" under "Account Name"/Column G). Similarly, I got Totals Monthly C91 by referring to Transactions H79 (...month of May...)
• Totals Monthly B93 refers to Transactions H10
• Totals Monthly C93 refers to Transactions H81
• Etc., Etc.

Maybe with the full data, ya'll will notice something important I didn't make clear in my first post with the simulated table. Thanks much!

Master Accounting.xlsb.xlsx
	A	B	C	D	E	F	G	H
1	Date	Merchant	Amount	SubCategory	Category	Parent	Account Name	Account Total
2	06/27/2022	WWV	100	AM Daily Sorrel	Tips	Cash	Cash	0
3	06/27/2022	EUCCU	500	Transfer	Transfer	Banking	EUCCU Visa	500
4	06/27/2022	EUCCU	-500	Transfer	Transfer	Banking	EUCCU Checking	3262.34
5	06/27/2022	EUCCU	-1565	Withdrawal	Transfer	Banking	Cash	-100
6	06/27/2022	EUCCU	782.5	Deposit	Transfer	Banking	EUCCU Savings	4264.5
7	06/27/2022	EUCCU	782.5	Deposit	Transfer	Banking	EUCCU Checking	3762.34
8	06/26/2022	Deborah	-10	Shuttle Driver	Tips	Cash	Cash	1465
9	06/26/2022	WWV	100	AM Daily Sorrel	Tips	Cash	Cash	1475
10	06/25/2022	T & H CORNER Store	-10.75	Loose	Tobacco	Smokables	BofA Checking	3152.4
11	06/25/2022	Food Truck Park	93	Busking	Music Income	Cash	Cash	1375
12	06/25/2022	WWV	50	Ropes Course	Tips	Cash	Cash	1282
13	06/24/2022	98 Center	96.7	98 Center	Paycheck	Digital	BofA Checking	3163.15
14	06/24/2022	Deborah	-10	Shuttle Driver	Tips	Cash	Cash	1232
15	06/24/2022	WWV	100	LC Ducky	Tips	Cash	Cash	1242
16	06/23/2022	T & H CORNER Store	-9.14	Pack	Tobacco	Smokables	BofA Checking	3066.45
17	06/23/2022	Food Truck Park	64	Busking	Music Income	Cash	Cash	1142
18	06/23/2022	WWV	80	Ropes Course	Tips	Digital	Venmo	250
19	06/22/2022	City Market	-76.09	Local	Groceries	Food and Drink	BofA Checking	3075.59
20	06/21/2022	WWV	60	AM Daily	Tips	Cash	Cash	1078
21	06/21/2022	Crystal Shop	48	Busking	Music Income	Cash	Cash	1018
22	06/20/2022	Maverick	-8.4	Pack	Tobacco	Smokables	BofA Checking	3151.68
23	06/20/2022	Maverick	-1.93	Travelling	Coffee Shops	Food and Drink	BofA Checking	3160.08
24	06/19/2022	Smith's	-6.79	Travelling	Groceries	Food and Drink	BofA Checking	3162.01
25	06/19/2022	Harmons SLC	-3.09	Travelling	Groceries	Food and Drink	BofA Checking	3168.8
26	06/19/2022	Higher Ground Coffee SLC	-3	Travelling	Coffee Shops	Food and Drink	Cash	970
27	06/18/2022	Speedway SLC	-33.32	SLC with Jeff	Public Transportation	Transportation	BofA Checking	3171.89
28	06/18/2022	Stonefed	288	Hogwallow	Music Income	Cash	Cash	973
29	06/17/2022	WWV	987.1	WWV	Paycheck	Digital	BofA Checking	3205.21
30	06/17/2022	Stonefed	270	Hogwallow	Music Income	Cash	Cash	685
31	06/17/2022	Accounting Error	-90	Accounting Error	Accounting Error	Cash	Cash	415
32	06/17/2022	Bank of America	600	Transfer	Transfer	Banking	BofA Savings	3097.76
33	06/17/2022	Bank of America	-600	Transfer	Transfer	Banking	BofA Checking	2218.11
34	06/16/2022	Adam	-10	Shuttle Driver	Tips	Digital	Venmo	170
35	06/16/2022	WWV	100	LC Daily	Tips	Digital	Venmo	180
36	06/15/2022	Gena	-10	Shuttle Driver	Tips	Cash	Cash	505
37	06/15/2022	WWV	80	Full Daily	Tips	Cash	Cash	515
38	06/14/2022	WWV	50	Ropes Course	Tips	Cash	Cash	435
39	06/14/2022	Gena	-5	Shuttle Driver	Tips	Cash	Cash	385
40	06/14/2022	WWV	30	LC Raft	Tips	Cash	Cash	390
41	06/13/2022	WWV	40	Ropes Course	Tips	Digital	Venmo	80
42	06/12/2022	Gena	-5	Shuttle Driver	Tips	Cash	Cash	360
43	06/12/2022	WWV	40	LC Daily	Tips	Cash	Cash	365
44	06/11/2022	WWV	80	LC Daily Sorrel	Tips	Cash	Cash	325
45	06/10/2022	98 Center	489.25	98 Center	Paycheck	Digital	BofA Checking	2818.11
46	06/10/2022	WWV	100	LC Daily	Tips	Cash	Cash	245
47	06/10/2022	City Market	-27.2	Nicotine Gum	Tobacco	Smokables	BofA Checking	2328.86
48	06/09/2022	WWV	40	Rentals	Tips	Cash	Cash	145
49	06/09/2022	WWV	40	AM Ducky	Tips	Cash	Cash	105
50	06/08/2022	WWV	65	LC Daily	Tips	Cash	Cash	65
51	06/08/2022	Accounting Error	-100.7	Accounting Error	Accounting Error	Cash	Cash	0
52	06/08/2022	EUCCU	-3688	Withdrawal	Transfer	Banking	Cash	100.7
53	06/08/2022	EUCCU	1844	Deposit	Transfer	Banking	EUCCU Savings	3482
54	06/08/2022	EUCCU	1844	Deposit	Transer	Banking	EUCCU Checking	2979.84
55	06/07/2022	Gear Traders	-2.36	Bike Lock	Service & Parts	Transportation	BofA Checking	2356.06
56	06/07/2022	Mark	-10	Shuttle Driver	Tips	Cash	Cash	3788.7
57	06/07/2022	WWV	110	AM Daily	Tips	Cash	Cash	3798.7
58	06/06/2022	Maverick	-10.11	Pack	Tobacco	Smokables	BofA Checking	2358.42
59	06/05/2022	WWV	50	AM Daily	Tips	Cash	Cash	3688.7
60	06/04/2022	City Market	-5.45	Local	Groceries	Food and Drink	BofA Checking	2368.53
61	06/04/2022	Stonefed	280	Woody's	Music Income	Cash	Cash	3638.7
62	06/04/2022	WWV	100	LC Daily Sorrel	Tips	Cash	Cash	3358.7
63	06/04/2022	WWV	698.27	WWV	Paycheck	Digital	BofA Checking	2373.98
64	06/03/2022	Stonefed	305	Woody's	Music Income	Cash	Cash	3258.7
65	06/03/2022	Sam Welch (Navtec)	50	Gifts	Gifts Received	Gifts	Cash	2953.7
66	06/02/2022	Chevron Moab	-27.72	Loose	Tobacco	Smokables	Cash	2903.7
67	06/02/2022	Gena	-10	Shuttle Driver	Tips	Cash	Cash	2931.42
68	06/02/2022	WWV	20	LC Daily	Tips	Digital	Venmo	40
69	06/02/2022	City Market	-16.21	Local	Groceries	Food and Drink	BofA Checking	1675.71
70	06/02/2022	WWV	100	LC Daily	Tips	Cash	Cash	2941.42
71	06/02/2022	WWV	60	Ropes Course	Tips	Cash	Cash	2841.42
72	06/01/2022	Wire Transfer Fee	-3	Bank Fee	Service Fee	Banking	BofA Checking	1691.92
73	06/01/2022	Theresa	-300	Park Lane	Rent	Lodging	Cash	2781.42
74	06/01/2022	Christian and Jessica	280	Dogsitting	Side Hustles	Cash	Cash	3081.42
75	06/01/2022	Salt Lake Express	-71.85	Bus Ticket back to Moab	Public Transportation	Transportation	BofA Checking	1694.92
76	06/01/2022	FB Marketplace	25	Garmin GPS unit	Side Hustles	Cash	Cash	2801.42
77	06/01/2022	The Land	-860	Withdrawal	Land Withdrawal	The Land	BofA Checking	1766.77
78	06/01/2022	The Land	860	Deposit	Land Deposit	The Land	Real Estate Investment	23440
79	05/31/2022	Gena	-40	Shuttle Driver	Tips	Cash	Cash	2776.42
80	05/31/2022	WWV	200	LC Daily Sorrel	Tips	Cash	Cash	2816.42
81	05/30/2022	Maverick	-10.11	Loose	Tobacco	Smokables	BofA Checking	2626.77
82	05/28/2022	Meandercat	450	Woody's	Music Income	Cash	Cash	2616.42
83	05/28/2022	T & H CORNER Store	-9	Loose	Tobacco	Smokables	Cash	2166.42
84	05/28/2022	WWV	30	LC Daily	Tips	Cash	Cash	2175.42
85	05/28/2022	WWV	40	Ropes Course	Tips	Cash	Cash	2145.42
86	05/27/2022	Drunk at the bar	1	Loose	Tobacco	Smokables	Cash	2105.42
87	05/27/2022	City Market	-2.58	Local	Groceries	Food and Drink	Cash	2104.42
88	05/27/2022	WWV	80	LC Ducky	Tips	Cash	Cash	2107
89	05/26/2022	True Value	-7.06	JB Weld	Service & Parts	Shopping	BofA Checking	2636.88
90	05/26/2022	98 Center	351.41	98 Center	Paycheck	Digital	BofA Checking	2643.94
91	05/26/2022	City Market	-10.63	Local	Groceries	Food and Drink	BofA Checking	2292.53
92	05/26/2022	RR Moab	-9.79	Loose	Tobacco	Smokables	BofA Checking	2303.16
93	05/26/2022	USPS (mailing forgotten stuff back to dad from stillwater trip)	-13.85	Shipping	Shipping	Services	BofA Checking	2312.95
94	05/25/2022	Kevin	-10	Shuttle Driver	Tips	Digital	Venmo	20
95	05/25/2022	WWV	100	AM Daily	Tips	Cash	Cash	2027
96	05/24/2022	WWV	50	Full Daily	Tips	Cash	Cash	1927
97	05/23/2022	T & H CORNER Store	-10.75	Loose	Tobacco	Smokables	BofA Checking	2326.8
98	05/22/2022	Higher Ground Coffee SLC	-2	Travelling	Coffee Shops	Food and Drink	Cash	1877
99	05/22/2022	Olympus Hills SLC gas	-57.61	SLC with Jeff	Public Transportation	Transportation	BofA Checking	2337.55
100	05/22/2022	Smith's	-7.29	Travelling	Groceries	Food and Drink	BofA Checking	2395.16
101	05/21/2022	Stonefed	275	Hogwallow	Music Income	Cash	Cash	1879
102	05/21/2022	Maverick	-8.84	Pack	Tobacco	Smokables	BofA Checking	2402.45
103	05/20/2022	WWV	120	LC Daily	Tips	Cash	Cash	1604
104	05/20/2022	WWV	20	Ropes Course	Tips	Cash	Cash	1484
105	05/20/2022	Walker Drug	-2.82	Papers	Tobacco	Smokables	BofA Checking	2411.29
106	05/19/2022	Dao	-10	Shuttle Driver	Tips	Cash	Cash	1464
107	05/19/2022	WWV	100	AM Daily	Tips	Cash	Cash	1474
108	05/19/2022	NOLS	-385	WFR	Certifications	Education	BofA Checking	2414.11
109	05/15/2022	T & H CORNER Store	-1.95	Local	Groceries	Food and Drink	BofA Checking	2799.11
110	05/15/2022	Texs Riverways	-80	Tours	Public Transportation	Transportation	Cash	1374
111	05/15/2022	T & H CORNER Store	-29.27	Loose	Tobacco	Smokables	BofA Checking	2801.06
112	05/13/2022	98 Center	290.1	98 Center	Paycheck	Digital	BofA Checking	2830.33
113	05/08/2022	Theresa	-40	Tours	Sporting Goods	Services	Cash	1454
114	05/08/2022	Texs Riverways	-233.72	Tours	Public Transportation	Transportation	BofA Checking	2540.23
115	05/07/2022	Stonefed	526	Woody's	Music Income	Cash	Cash	1494
116	05/07/2022	T & H CORNER Store	-13.73	Loose	Tobacco	Smokables	BofA Checking	2773.95
117	05/06/2022	WWV	335.29	WWV	Paycheck	Digital	BofA Checking	2787.68
118	05/05/2022	Moab Landscaping	192.02	Landscaping	Paycheck	Digital	BofA Savings	2497.76
119	05/04/2022	Moonflower	-21.84	Local	Groceries	Food and Drink	BofA Checking	2452.39
120	05/04/2022	Gear Traders	-39.18	30 wag bags	Sporting Goods	Shopping	BofA Checking	2474.23
121	05/04/2022	Gear Traders	-17.39	200' Paracord	Sporting Goods	Shopping	BofA Checking	2513.41
122	05/04/2022	City Market	-131.82	Local	Groceries	Food and Drink	BofA Checking	2530.8
123	05/03/2022	Maverick	-10.44	Pack	Tobacco	Smokables	BofA Checking	2662.62
124	05/03/2022	Theresa	-250	Park Lane	Rent	Lodging	Cash	968
125	05/02/2022	Wire Transfer Fee	-3	Bank Fee	Service Fee	Banking	BofA Checking	2673.06
126	05/02/2022	True Value	-8.7	Plastic Weld	Hobbies	Shopping	BofA Checking	2676.06
127	05/02/2022	Walker Drug	-2.49	Headphones	Electronics	Shopping	BofA Checking	2684.76
128	05/02/2022	The Land	-860	Withdrawal	Land Withdrawal	The Land	BofA Checking	2687.25
129	05/02/2022	The Land	860	Deposit	Land Deposit	The Land	Real Estate Investment	22580
Transactions

Cell Formulas
Range	Formula
H2:H129	H2	=SUMIFS($C2:$C$332,$G2:G$332,$G2)+SUMIFS($J$328:$N$328,$J$327:$N$327,$G2)

Master Accounting.xlsb.xlsx
	A	B	C
90	Liquid Assets	$ 14,531.00	$ 10,694.79
91	Cash	$ -	$ 2,776.42
92	Debit	$ 14,531.00	$ 7,918.37
93	BofA Checking	$ 3,152.40	$ 2,626.77
94	BofA Savings	$ 3,097.76	$ 2,497.76
95	EUCCU Checking	$ 3,262.34	$ 1,135.84
96	EUCCU Savings	$ 4,264.50	$ 1,638.00
97	EUCCU Visa	$ 500.00
98	Investment Assets	$ 4.00
99	Venmo	$ 250.00	$ 20.00
Totals Monthly

Cell Formulas
Range	Formula
B90:C90	B90	=SUM(B$91:B$92)
B92:C92	B92	=SUM(B$93:B$99)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B90:CA103	Cell Value	<0	text	NO
B90:CA103	Cell Value	>0	text	NO
B90:CA103	Cell Value	=0	text	NO

 

[Peter_SSs]

Thanks for the sample data.
Assuming the table on sheet 'Transactions' is also called Transactions, try this.
A few results are different to your sample I believe due to you using the full 'Transactions' table but we only had a part of that table.

Dudetrek.xlsm
	A	B	C
90	Liquid Assets	14,527.00	7,920.95
91	Cash	0.00	2,776.42
92	Debit	14,527.00	5,144.53
93	BofA Checking	3,152.40	2,626.77
94	BofA Savings	3,097.76	2,497.76
95	EUCCU Checking	3,262.34
96	EUCCU Savings	4,264.50
97	EUCCU Visa	500.00
98	Investment Assets
99	Venmo	250.00	20.00
Totals Monthly

Cell Formulas
Range	Formula
B90:C90	B90	=SUM(B$91:B$92)
B91,B93:B99	B91	=INDEX(FILTER(Transactions[Account Total],(Transactions[Account Name]=A91)*(TEXT(Transactions[Date],"mmyy")=TEXT(TODAY(),"mmyy")),""),1)
C91,C93:C99	C91	=INDEX(FILTER(Transactions[Account Total],(Transactions[Account Name]=A91)*(TEXT(Transactions[Date],"mmyy")=TEXT(EDATE(TODAY(),-1),"mmyy")),""),1)
B92:C92	B92	=SUM(B$93:B$99)