Insert Value Based on Multiple Criteria

[MrDB4Excel]

I am throwing in the towel. I have searched multiple sites this morning offering a multitude of solutions, all of which I have tried but to no avail.
Two solutions you will see in the attached Xl2bb in column J are represented by “#VALUE!” and “#N/A” and an additional solution in column J that gave the result of the formula “=LOOKUP(F4,R3:R9,Q3:Q9)” as Electricity when it should have returned VZP.
I would like help with two things.
In column D you see Category. Currently, I either type this category or select the category from a drop-down to indicate what category the indicated Kč amount is shown in column F.
Column I shows the full name of the month that is indicated by the month number in column A. As you can see January worked fine but February and March returns “#VALUE!” when what it should do is return the month full name based upon the corresponding value in column A.
Columns K & L are simply total type columns. Column L changes data dependent upon the value input in cell J1.
Other columns’ cells may simply be helper cells as indicated by their presence in formulas.
Attached are two Xl2bb files: BogusEasyAccounting2022.xlsx & BogusAutomatedMonthlyBudget.xlsx

Any help will be greatly appreciated.

BogusEasyAccounting2022.xlsx
	A	B	C	D	E	F
2	Month	Date	Description	Debits	Income	Balance
3	1	Jan/06/2022	January Bogus Starting Balance			20,000.00 Kč
4	1	Jan/06/2022	Kč Cash deposit		20,000.00 Kč	40,000.00 Kč
5	1	Jan/06/2022	January 2021 VZP	2,187.00 Kč		37,813.00 Kč
6	1	Jan/06/2022	January 2021 VZP	2,187.00 Kč		35,626.00 Kč
7	1	Jan/07/2022	1693 www.cd.cz/eshop/ ML2Praha	53.00 Kč		35,573.00 Kč
8	1	Jan/07/2022	1693 www.cd.cz/eshop/ Praha2ML	65.00 Kč		35,508.00 Kč
9	1	Jan/08/2022	December Mobile Service	220.00 Kč		35,288.00 Kč
10	1	Jan/08/2022	Bogus Miscellaneous	136.01 Kč		35,151.99 Kč
11	1	Jan/14/2022	Annual Internet	3,999.00 Kč		31,152.99 Kč
12	1	Jan/14/2022	Electricity for January 2022	950.00 Kč		30,202.99 Kč
13	1	Jan/14/2022	Cellar Storage for January 2022	300.00 Kč		29,902.99 Kč
14	1	Jan/14/2022	Utilities for January 2022	4,027.00 Kč		25,875.99 Kč
15	1	Jan/28/2022	US EMBASSY PRAGUE	2,245.60 Kč		23,630.39 Kč
16	2	Feb/03/2022	Bogus Miscellaneous	3,525.53 Kč		20,104.86 Kč
17	2	Feb/08/2022	Kč Cash deposit		20,000.00 Kč	40,104.86 Kč
18	2	Feb/06/2022	January 2021 VZP	2,187.00 Kč		37,917.86 Kč
19	2	Feb/06/2022	January 2021 VZP	2,187.00 Kč		35,730.86 Kč
20	2	Feb/07/2022	Bogus Miscellaneous	5,072.00 Kč		30,658.86 Kč
21	2	Feb/09/2022	January Mobile Service	220.00 Kč		30,438.86 Kč
22	2	Feb/09/2022	Bogus Miscellaneous	136.01 Kč		30,302.85 Kč
23	2	Feb/14/2022	Electricity for February 2022	950.00 Kč		29,352.85 Kč
24	2	Feb/14/2022	Cellar Storage for February 2022	300.00 Kč		29,052.85 Kč
25	2	Feb/14/2022	Utilities for February 2022	4,027.00 Kč		25,025.85 Kč
26	3	Mar/06/2022	March 2022 VZP	2,187.00 Kč		22,838.85 Kč
27	3	Mar/06/2022	March 2022 VZP	2,187.00 Kč		20,651.85 Kč
28	3	Mar/07/2022	Kč Cash deposit		20,000.00 Kč	40,651.85 Kč
29	3	Mar/08/2022	February Mobile Service	220.00 Kč		40,431.85 Kč
30	3	Mar/11/2022	Bogus Miscellaneous	136.01 Kč		40,295.84 Kč
31	3	Mar/14/2022	Electricity for March 2022	950.00 Kč		39,345.84 Kč
32	3	Mar/14/2022	Cellar Storage for March 2022	300.00 Kč		39,045.84 Kč
33	3	Mar/14/2022	Utilities for March 2022	4,027.00 Kč		35,018.84 Kč
34	3	Mar/17/2022	Bogus Miscellaneous	4,826.13 Kč		30,192.71 Kč
35	3	Mar/27/2022	Bogus Miscellaneous	1,269.00 Kč		28,923.71 Kč
36	1					28,923.71 Kč
37	1					28,923.71 Kč
Expenses

Cell Formulas
Range	Formula
A3:A37	A3	=MONTH(B3)
F4:F37	F4	=SUM(F3+[@Income]-[@Debits])

BogusAutomatedMonthlyBudget.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Month	Date	Description	Category	Income	Debits	Balance			1	Running Totals	Totals					Regular Monthly or Annual Expense	Non-Regular Expense
2	1	Jan/06/2022	January Bogus Starting Balance	Miscellaneous	0.00 Kč		20,000.00 Kč	1	January	Income	60,000.00 Kč	20,000.00 Kč	1	January	N2		0.00 Kč	0.00 Kč
3	1	Jan/06/2022	Kč Cash deposit	Income	20,000.00 Kč		40,000.00 Kč	1		Building Utilities	12,081.00 Kč	4,027.00 Kč	2	February	N3		VZP	2,187.00 Kč
4	1	Jan/06/2022	January 2021 VZP	VZP		2,187.00 Kč	37,813.00 Kč	1		Cellar	900.00 Kč	300.00 Kč	3	March	N4		Cellar	300.00 Kč
5	1	Jan/06/2022	January 2021 VZP	VZP		2,187.00 Kč	35,626.00 Kč	1		CEZ Mobile Phone	660.00 Kč	220.00 Kč	4	April	N5		Electricity	950.00 Kč
6	1	Jan/07/2022	1693 www.cd.cz/eshop/ ML2Praha	Miscellaneous		53.00 Kč	35,573.00 Kč	1		Electricity	2,850.00 Kč	950.00 Kč	5	May	N6		Building Utilities	4,027.00 Kč
7	1	Jan/07/2022	1693 www.cd.cz/eshop/ Praha2ML	Miscellaneous		65.00 Kč	35,508.00 Kč	1		Foreign ATM Withdrawal	0.00 Kč	0.00 Kč	6	June	N7		TaNet	3,999.00 Kč
8	1	Jan/08/2022	December Mobile Service	CEZ Mobile Phone		220.00 Kč	35,288.00 Kč	1		Miscellaneous	17,464.29 Kč	2,499.61 Kč	7	July	N8		CEZ Mobile Phone	356.01 Kč
9	1	Jan/08/2022	Bogus Miscellaneous	Miscellaneous		136.01 Kč	35,151.99 Kč	1		Property Tax	0.00 Kč	0.00 Kč	8	August	N9		Property Tax	1,218.00 Kč
10	1	Jan/14/2022	Annual Internet	TaNet		3,999.00 Kč	31,152.99 Kč	1		TaNet	3,999.00 Kč	3,999.00 Kč	9	September	N10
11	1	Jan/14/2022	Electricity for January 2022	Electricity		950.00 Kč	30,202.99 Kč	1		VZP	13,122.00 Kč	4,374.00 Kč	10	October	N11
12	1	Jan/14/2022	Cellar Storage for January 2022	Cellar		300.00 Kč	29,902.99 Kč	1					11	November	N12
13	1	Jan/14/2022	Utilities for January 2022	Building Utilities		4,027.00 Kč	25,875.99 Kč	1		SubTotal Debits	51,076.29 Kč		12	December	N13
14	1	Jan/28/2022	US EMBASSY PRAGUE	Miscellaneous		2,245.60 Kč	23,630.39 Kč	1
15	2	Feb/03/2022	Bogus Miscellaneous	Miscellaneous		3,525.53 Kč	20,104.86 Kč	2	#VALUE!	2021 Mobile Phone	8,923.71 Kč
16	2	Feb/08/2022	Kč Cash deposit	Income	20,000.00 Kč		40,104.86 Kč	2
17	2	Feb/06/2022	January 2021 VZP	VZP		2,187.00 Kč	37,917.86 Kč	2		28,923.71 Kč
18	2	Feb/06/2022	January 2021 VZP	VZP		2,187.00 Kč	35,730.86 Kč	2
19	2	Feb/07/2022	Bogus Miscellaneous	Miscellaneous		5,072.00 Kč	30,658.86 Kč	2		#VALUE!
20	2	Feb/09/2022	January Mobile Service	CEZ Mobile Phone		220.00 Kč	30,438.86 Kč	2		2,187.00 Kč
21	2	Feb/09/2022	Bogus Miscellaneous	Miscellaneous		136.01 Kč	30,302.85 Kč	2		300.00 Kč
22	2	Feb/14/2022	Electricity for February 2022	Electricity		950.00 Kč	29,352.85 Kč	2		950.00 Kč
23	2	Feb/14/2022	Cellar Storage for February 2022	Cellar		300.00 Kč	29,052.85 Kč	2		4,027.00 Kč
24	2	Feb/14/2022	Utilities for February 2022	Building Utilities		4,027.00 Kč	25,025.85 Kč	2		3,999.00 Kč
25	3	Mar/06/2022	March 2022 VZP	VZP		2,187.00 Kč	22,838.85 Kč	3		356.01 Kč
26	3	Mar/06/2022	March 2022 VZP	VZP		2,187.00 Kč	20,651.85 Kč	3		1,218.00 Kč
27	3	Mar/07/2022	Kč Cash deposit	Income	20,000.00 Kč		40,651.85 Kč	3				January	1
28	3	Mar/08/2022	February Mobile Service	CEZ Mobile Phone		220.00 Kč	40,431.85 Kč	3		Electricity		#VALUE!	2	1
29	3	Mar/11/2022	Bogus Miscellaneous	Miscellaneous		136.01 Kč	40,295.84 Kč	3		#N/A		#VALUE!	3	1
30	3	Mar/14/2022	Electricity for March 2022	Electricity		950.00 Kč	39,345.84 Kč	3	#VALUE!			#N/A	4	1
31	3	Mar/14/2022	Cellar Storage for March 2022	Cellar		300.00 Kč	39,045.84 Kč	3				#N/A	5	1
32	3	Mar/14/2022	Utilities for March 2022	Building Utilities		4,027.00 Kč	35,018.84 Kč	3				#N/A	6	1
33	3	Mar/17/2022	Bogus Miscellaneous	Miscellaneous		4,826.13 Kč	30,192.71 Kč	3				#N/A	7	1
34	3	Mar/27/2022	Bogus Miscellaneous	Miscellaneous		1,269.00 Kč	28,923.71 Kč	3				#N/A	8	1
35							28,923.71 Kč					#N/A	9	1
36							28,923.71 Kč					#N/A	10	1
2022

Cell Formulas
Range	Formula
A2:A36	A2	=IF((ISERROR(MONTH('2022'!$B2)))," ",(MONTH('2022'!$B2)))
B2:B36	B2	=IF(ISBLANK([BogusEasyAccounting2022.xlsx]Expenses!$B3)," ",([BogusEasyAccounting2022.xlsx]Expenses!$B3))
C2:C36	C2	=IF(ISBLANK([BogusEasyAccounting2022.xlsx]Expenses!$C3)," ",([BogusEasyAccounting2022.xlsx]Expenses!$C3))
G2	G2	=[BogusEasyAccounting2022.xlsx]Expenses!$F$3
H2:H36	H2	=IF((ISERROR(VALUE(A2)))," ",(VALUE(A2)))
I2	I2	=XLOOKUP(M2,$A$2:$A$36,$N$2:$N$13,0)
K2	K2	=SUMIF(D:D,J2,E:E)
L2	L2	=SUMIFS(Table23[Income],Table23[Month],$J$1,Table23[Category],J2)
K3:K11	K3	=SUMIF(D:D,J3,F:F)
L3:L11	L3	=SUMIFS(Table23[Debits],Table23[Month],$J$1,Table23[Category],J3)
Q2	Q2	=SUMPRODUCT(--(F:F=N16),D:D)
R2	R2	=SUMPRODUCT(--(E:E=N17),D:D)
E3	E3	=[BogusEasyAccounting2022.xlsx]Expenses!$E$4
G3	G3	=SUM(G2+[@Income]-[@Debits])
E4:E36	E4	=IF(ISBLANK([BogusEasyAccounting2022.xlsx]Expenses!$E5)," ",([BogusEasyAccounting2022.xlsx]Expenses!$E5))
F4:F36	F4	=IF(ISBLANK([BogusEasyAccounting2022.xlsx]Expenses!$D5)," ",([BogusEasyAccounting2022.xlsx]Expenses!$D5))
G4:G36	G4	=G3+N([@Income])-N([@Debits])
K13	K13	=SUM(K3:K11)
I15	I15	=XLOOKUP(M3,$A$2:$A$36,$N$2:$N$13,0)
K15	K15	=SUM(K2-K13)
J17	J17	=LOOKUP(2,1/(G:G<>""),G:G)
J19	J19	=IF(ISNUMBER(MATCH(F4,$R$3:$R$9,0)),Q2:Q11)
J20:J26	J20	=SUMPRODUCT(($Q$3:$Q$9=Q3)*($R$3:$R$9=R3)*($R$3:$R$9))
J28	J28	=LOOKUP(F4,R3:R9,Q3:Q9)
J29	J29	=XLOOKUP(F4,Q3:Q9&R3:R9,0)
I30	I30	=XLOOKUP(M4,$H$2:$H$36,$N$2:$N$13,0)
L27:L36	L27	=XLOOKUP(M2,$H$2:$H$36,$N$2:$N$13,0)
N28:N36	N28	=IF((ISERROR(VALUE(A5)))," ",(VALUE(A5)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2	Other Type	DataBar		NO

Cells with Data Validation
Cell	Allow	Criteria
D1	List	SSB-VZP, DDB-VZP, Dvorakova 533/2 Misc, CEZ Mobile Phone, Income, Product, Miscellaneous, CSOB Account Charges
D2:D36	List	Building Utilities, Cellar, CEZ Mobile Phone, Electricity, Foreign ATM Withdrawal, Income, Miscellaneous, Property Tax, TaNet, VZP

 

[MrDB4Excel]

Is there anyone out here in the Mr. Excel forum that perhaps has any suggestions on how this can be resolved?
Needless to say, I am getting quite frustrated. Again today I tinkered with this, trying several different options, and again came up without anything to fix this.
The attached Xl2bb shows column D with red text and a yellow fill, this is the column that needs to have the formula that would get from Q3 to Q9 the text to input based on the number seen in column F and the relevant row so that for example when the number 2187 is entered into F4 then cell D4 is auto-filled with VZP.

BogusAutomatedMonthlyBudget.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Month	Date	Description	Category	Income	Debits	Balance			1	Running Totals	Totals					Regular Monthly or Annual Expense	Non-Regular Expense	Amount
2	1	Jan/06/2022	January Bogus Starting Balance	Miscellaneous	0.00 Kč		20,000.00 Kč	1	January	Income	60,000.00 Kč	20,000.00 Kč	1	January	N2		0.00 Kč	0.00 Kč	2187.00
3	1	Jan/06/2022	Kč Cash deposit	Income	20,000.00 Kč		40,000.00 Kč	1		Building Utilities	12,081.00 Kč	4,027.00 Kč	2	February	N3		VZP	2,187.00 Kč	300.00
4	1	Jan/06/2022	January 2021 VZP	VZP		2,187.00 Kč	37,813.00 Kč	1		Cellar	900.00 Kč	300.00 Kč	3	March	N4		Cellar	300.00 Kč	950.00
5	1	Jan/06/2022	January 2021 VZP	VZP		2,187.00 Kč	35,626.00 Kč	1		CEZ Mobile Phone	660.00 Kč	220.00 Kč	4	April	N5		Electricity	950.00 Kč	4027.00
6	1	Jan/07/2022	1693 www.cd.cz/eshop/ ML2Praha	Miscellaneous		53.00 Kč	35,573.00 Kč	1		Electricity	2,850.00 Kč	950.00 Kč	5	May	N6		Building Utilities	4,027.00 Kč	3999.00
7	1	Jan/07/2022	1693 www.cd.cz/eshop/ Praha2ML	Miscellaneous		65.00 Kč	35,508.00 Kč	1		Foreign ATM Withdrawal	0.00 Kč	0.00 Kč	6	June	N7		TaNet	3,999.00 Kč	356.01
8	1	Jan/08/2022	December Mobile Service	CEZ Mobile Phone		220.00 Kč	35,288.00 Kč	1		Miscellaneous	17,464.29 Kč	2,499.61 Kč	7	July	N8		CEZ Mobile Phone	356.01 Kč	1218.00
9	1	Jan/08/2022	Bogus Miscellaneous	Miscellaneous		136.01 Kč	35,151.99 Kč	1		Property Tax	0.00 Kč	0.00 Kč	8	August	N9		Property Tax	1,218.00 Kč
10	1	Jan/14/2022	Annual Internet	TaNet		3,999.00 Kč	31,152.99 Kč	1		TaNet	3,999.00 Kč	3,999.00 Kč	9	September	N10
11	1	Jan/14/2022	Electricity for January 2022	Electricity		950.00 Kč	30,202.99 Kč	1		VZP	13,122.00 Kč	4,374.00 Kč	10	October	N11
12	1	Jan/14/2022	Cellar Storage for January 2022	Cellar		300.00 Kč	29,902.99 Kč	1					11	November	N12
13	1	Jan/14/2022	Utilities for January 2022	Building Utilities		4,027.00 Kč	25,875.99 Kč	1		SubTotal Debits	51,076.29 Kč		12	December	N13
14	1	Jan/28/2022	US EMBASSY PRAGUE	Miscellaneous		2,245.60 Kč	23,630.39 Kč	1
15	2	Feb/03/2022	Bogus Miscellaneous	Miscellaneous		3,525.53 Kč	20,104.86 Kč	2	#VALUE!	2021 Mobile Phone	8,923.71 Kč
16	2	Feb/08/2022	Kč Cash deposit	Income	20,000.00 Kč		40,104.86 Kč	2
17	2	Feb/06/2022	January 2021 VZP	VZP		2,187.00 Kč	37,917.86 Kč	2		28,923.71 Kč
18	2	Feb/06/2022	January 2021 VZP	VZP		2,187.00 Kč	35,730.86 Kč	2				#N/A
19	2	Feb/07/2022	Bogus Miscellaneous	Miscellaneous		5,072.00 Kč	30,658.86 Kč	2		#VALUE!
20	2	Feb/09/2022	January Mobile Service	CEZ Mobile Phone		220.00 Kč	30,438.86 Kč	2		2,187.00 Kč
21	2	Feb/09/2022	Bogus Miscellaneous	Miscellaneous		136.01 Kč	30,302.85 Kč	2		300.00 Kč
22	2	Feb/14/2022	Electricity for February 2022	Electricity		950.00 Kč	29,352.85 Kč	2		950.00 Kč
23	2	Feb/14/2022	Cellar Storage for February 2022	Cellar		300.00 Kč	29,052.85 Kč	2		4,027.00 Kč
24	2	Feb/14/2022	Utilities for February 2022	Building Utilities		4,027.00 Kč	25,025.85 Kč	2		3,999.00 Kč
25	3	Mar/06/2022	March 2022 VZP	VZP		2,187.00 Kč	22,838.85 Kč	3		356.01 Kč
26	3	Mar/06/2022	March 2022 VZP	VZP		2,187.00 Kč	20,651.85 Kč	3		1,218.00 Kč
27	3	Mar/07/2022	Kč Cash deposit	Income	20,000.00 Kč		40,651.85 Kč	3				January	1
28	3	Mar/08/2022	February Mobile Service	CEZ Mobile Phone		220.00 Kč	40,431.85 Kč	3		Electricity		#VALUE!	2	1
29	3	Mar/11/2022	Bogus Miscellaneous	Miscellaneous		136.01 Kč	40,295.84 Kč	3		#N/A		#VALUE!	3	1
30	3	Mar/14/2022	Electricity for March 2022	Electricity		950.00 Kč	39,345.84 Kč	3	#VALUE!	#VALUE!		#N/A	4	1
31	3	Mar/14/2022	Cellar Storage for March 2022	Cellar		300.00 Kč	39,045.84 Kč	3		#VALUE!		#N/A	5	1
32	3	Mar/14/2022	Utilities for March 2022	Building Utilities		4,027.00 Kč	35,018.84 Kč	3				#N/A	6	1
33	3	Mar/17/2022	Bogus Miscellaneous	Miscellaneous		4,826.13 Kč	30,192.71 Kč	3				#N/A	7	1
34	3	Mar/27/2022	Bogus Miscellaneous	Miscellaneous		1,269.00 Kč	28,923.71 Kč	3				#N/A	8	1
35							28,923.71 Kč					#N/A	9	1
36							28,923.71 Kč					#N/A	10	1
2022

Cell Formulas
Range	Formula
A2:A36	A2	=IF((ISERROR(MONTH('2022'!$B2)))," ",(MONTH('2022'!$B2)))
B2:B36	B2	=IF(ISBLANK('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$B3)," ",('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$B3))
C2:C36	C2	=IF(ISBLANK('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$C3)," ",('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$C3))
G2	G2	='F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$F$3
H2:H36	H2	=IF((ISERROR(VALUE(A2)))," ",(VALUE(A2)))
I2	I2	=XLOOKUP(M2,$A$2:$A$36,$N$2:$N$13,0)
K2	K2	=SUMIF(D:D,J2,E:E)
L2	L2	=SUMIFS(Table23[Income],Table23[Month],$J$1,Table23[Category],J2)
K3:K11	K3	=SUMIF(D:D,J3,F:F)
L3:L11	L3	=SUMIFS(Table23[Debits],Table23[Month],$J$1,Table23[Category],J3)
Q2	Q2	=SUMPRODUCT(--(F:F=N16),D:D)
R2	R2	=SUMPRODUCT(--(E:E=N17),D:D)
E3	E3	='F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$E$4
G3	G3	=SUM(G2+[@Income]-[@Debits])
D4	D4	=XLOOKUP(R3,$F$4:$F$34,$Q$3:$Q$9,0)
E4:E36	E4	=IF(ISBLANK('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$E5)," ",('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$E5))
F4:F36	F4	=IF(ISBLANK('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$D5)," ",('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$D5))
G4:G36	G4	=G3+N([@Income])-N([@Debits])
K13	K13	=SUM(K3:K11)
I15	I15	=XLOOKUP(M3,$A$2:$A$36,$N$2:$N$13,0)
K15	K15	=SUM(K2-K13)
J17	J17	=LOOKUP(2,1/(G:G<>""),G:G)
L18	L18	=XLOOKUP(Q3&R3,$D$2:$D$34&$R$3:$R$9,F4:F34)
J19	J19	=IF(ISNUMBER(MATCH(F4,$R$3:$R$9,0)),Q2:Q11)
J20:J26	J20	=SUMPRODUCT(($Q$3:$Q$9=Q3)*($R$3:$R$9=R3)*($R$3:$R$9))
I30	I30	=XLOOKUP(M4,$H$2:$H$36,$N$2:$N$13,0)
J28	J28	=LOOKUP(F4,R3:R9,Q3:Q9)
J29	J29	=XLOOKUP(F4,Q3:Q9&R3:R9,0)
J30	J30	=XLOOKUP(R3:R9,$F$4:$F$34,$Q$3:$Q$9,0)
J31	J31	=XLOOKUP(R4,$F$4:$F$34,$Q$3:$Q$9,0)
L27:L36	L27	=XLOOKUP(M2,$H$2:$H$36,$N$2:$N$13,0)
N28:N36	N28	=IF((ISERROR(VALUE(A5)))," ",(VALUE(A5)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2	Other Type	DataBar		NO

Cells with Data Validation
Cell	Allow	Criteria
D1	List	SSB-VZP, DDB-VZP, Dvorakova 533/2 Misc, CEZ Mobile Phone, Income, Product, Miscellaneous, CSOB Account Charges

 

[MrDB4Excel]

Not sure how to do this, but I found the solution as follows:
=IF(F4=$S$2,$Q$3,IF(F4=$S$3,$Q$4,IF(F4=$S$4,$Q$5,IF(F4=$S$5,$Q$6,IF(F4=$S$6,$Q$7,IF(F4=$S$7,$Q$8,IF(F4=$S$8,$Q$9,IF(F4<>$S$2,$J$8,IF(F4<>$S$3,$J$8,IF(F4<>$S$4,$J$8,IF(F4<>$S$5,$J$8,IF(F4<>$S$6,$J$8,IF(F4<>$S$7,$J$8,IF(F4<>$S$8,$J$8,IF(F4=$S$9,$J$8)))))))))))))))

Surely there is a simpler and shorter method but this works just fine. So if anyone has something else that is better let me know.
For now, I would mark this as the solution, but not sure how to do this or if I should be marking my own post as solved.

 

[jtakw]

Hi,

Try this in D4 copied down:

Book3.xlsx
	D		F				J							Q		S
1	Category		Debits				1							Regular Monthly or Annual Expense		Amount
2	Miscellaneous						Income									2187
3	Income						Building Utilities							VZP		300
4	VZP		2187				Cellar							Cellar		950
5	VZP		2187				CEZ Mobile Phone							Electricity		4027
6	Miscellaneous		53				Electricity							Building Utilities		3999
7	Miscellaneous		65				Foreign ATM Withdrawal							TaNet		356.01
8	Miscellaneous		220				Miscellaneous							CEZ Mobile Phone		1218
9	Miscellaneous		136.01				Property Tax							Property Tax
10	TaNet		3999				TaNet
11	Electricity		950				VZP
12	Cellar		300
13	Building Utilities		4027
14	Miscellaneous		2245.6
15	Miscellaneous		3525.53
16	Miscellaneous
17	VZP		2187
18	VZP		2187
19	Miscellaneous		5072
20	Miscellaneous		220
21	Miscellaneous		136.01
22	Electricity		950
23	Cellar		300
24	Building Utilities		4027
25	VZP		2187
26	VZP		2187
27	Miscellaneous
28	Miscellaneous		220
29	Miscellaneous		136.01
30	Electricity		950
31	Cellar		300
32	Building Utilities		4027
33	Miscellaneous		4826.13
34	Miscellaneous		1269
Sheet1087

Cell Formulas
Range	Formula
D4:D34	D4	=IFERROR(INDEX(Q$3:Q$9,MATCH(F4,S$2:S$8,0)),J$8)

 

[MrDB4Excel]

Hi,

Try this in D4 copied down:

Book3.xlsx
	D		F				J							Q		S
1	Category		Debits				1							Regular Monthly or Annual Expense		Amount
2	Miscellaneous						Income									2187
3	Income						Building Utilities							VZP		300
4	VZP		2187				Cellar							Cellar		950
5	VZP		2187				CEZ Mobile Phone							Electricity		4027
6	Miscellaneous		53				Electricity							Building Utilities		3999
7	Miscellaneous		65				Foreign ATM Withdrawal							TaNet		356.01
8	Miscellaneous		220				Miscellaneous							CEZ Mobile Phone		1218
9	Miscellaneous		136.01				Property Tax							Property Tax
10	TaNet		3999				TaNet
11	Electricity		950				VZP
12	Cellar		300
13	Building Utilities		4027
14	Miscellaneous		2245.6
15	Miscellaneous		3525.53
16	Miscellaneous
17	VZP		2187
18	VZP		2187
19	Miscellaneous		5072
20	Miscellaneous		220
21	Miscellaneous		136.01
22	Electricity		950
23	Cellar		300
24	Building Utilities		4027
25	VZP		2187
26	VZP		2187
27	Miscellaneous
28	Miscellaneous		220
29	Miscellaneous		136.01
30	Electricity		950
31	Cellar		300
32	Building Utilities		4027
33	Miscellaneous		4826.13
34	Miscellaneous		1269
Sheet1087

Cell Formulas
Range	Formula
D4:D34	D4	=IFERROR(INDEX(Q$3:Q$9,MATCH(F4,S$2:S$8,0)),J$8)

Excellent, thank you so much jtakw. Works very well for column F, but column E (Income) is a different matter. Thank you so much. So here is another Xl2bb after I input your formula and added to it slightly in hopes of picking up data from column S for the Income that appears in column E. Is it possible to get your formula to also work for column E?

AutomatedMonthlyBudget.xlsx
	D	E	F	G			J							Q	R	S
1	Category	Income	Debits	Balance			1							Regular Monthly or Annual Expense	Non-Regular Expense	Amount
2	Miscellaneous	0.00 Kč		54,566.97 Kč			Income							0.00 Kč	0.00 Kč	2187.00
3	Income	50,000.00 Kč		104,566.97 Kč			Building Utilities							VZP	2,187.00 Kč	300.00
4	VZP		2,187.00 Kč	102,379.97 Kč			Cellar							Cellar	300.00 Kč	950.00
5	VZP		2,187.00 Kč	100,192.97 Kč			CEZ Mobile Phone							Electricity	950.00 Kč	4027.00
6	Miscellaneous		53.00 Kč	100,139.97 Kč			Electricity							Building Utilities	4,027.00 Kč	3999.00
7	Miscellaneous		65.00 Kč	100,074.97 Kč			Foreign ATM Withdrawal							TaNet	3,999.00 Kč	356.01
8	CEZ Mobile Phone		356.01 Kč	99,718.96 Kč			Miscellaneous							CEZ Mobile Phone	356.01 Kč	1218.00
9	TaNet		3,999.00 Kč	95,719.96 Kč			Property Tax							Property Tax	1,218.00 Kč	10000.00
10	Electricity		950.00 Kč	94,769.96 Kč			TaNet							Income		20000.00
11	Cellar		300.00 Kč	94,469.96 Kč			VZP									30000.00
12	Building Utilities		4,027.00 Kč	90,442.96 Kč												40000.00
13	Miscellaneous		2,245.60 Kč	88,197.36 Kč			SubTotal Debits									50000.00
14	Miscellaneous		3,525.53 Kč	84,671.83 Kč
15	Miscellaneous	30,000.00 Kč		114,671.83 Kč			2021 Mobile Phone
16	VZP		2,187.00 Kč	112,484.83 Kč
17	VZP		2,187.00 Kč	110,297.83 Kč			44,116.68 Kč
2022

Cell Formulas
Range	Formula
G2	G2	='2021'!J17
Q2	Q2	=SUMPRODUCT(--(F:F=N16),D:D)
R2	R2	=SUMPRODUCT(--(E:E=N17),D:D)
E3	E3	=AI4
G3	G3	=SUM(G2+[@Income]-[@Debits])
D4:D17	D4	=IFERROR(INDEX(Q$3:Q$10,MATCH(F4,S$2:S$13,0)),J$8)
E4:E17	E4	=IF(ISBLANK('F:\Finances\CSOB\[EasyAccounting2022.xlsx]Expenses'!$E5)," ",('F:\Finances\CSOB\[EasyAccounting2022.xlsx]Expenses'!$E5))
F4:F17	F4	=IF(ISBLANK('F:\Finances\CSOB\[EasyAccounting2022.xlsx]Expenses'!$D5)," ",('F:\Finances\CSOB\[EasyAccounting2022.xlsx]Expenses'!$D5))
G4:G17	G4	=G3+N([@Income])-N([@Debits])
J17	J17	=LOOKUP(2,1/(G:G<>""),G:G)

 

[jtakw]

A few things.

1. You can Not change my INDEX/MATCH formula ranges as you did, the range sizes don't match, and will not produce correct results.
2. Your Column E and Column F formulas are putting a " " (space), in the cells that result TRUE, change that " " (space) to a nullstring ""
3. After you Fix point 2 above regarding the " " (space), you should be able to use this to check for Both Column E and Column F.

Book3.xlsx
	D	E	F				J							Q		S
1	Category	Income	Debits				1							Regular Monthly or Annual Expense		Amount
2	Miscellaneous	0					Income									2187
3	Income	50000					Building Utilities							VZP		300
4	VZP		2187				Cellar							Cellar		950
5	VZP		2187				CEZ Mobile Phone							Electricity		4027
6	Miscellaneous		53				Electricity							Building Utilities		3999
7	Miscellaneous		65				Foreign ATM Withdrawal							TaNet		356.01
8	Miscellaneous		220				Miscellaneous							CEZ Mobile Phone		1218
9	Miscellaneous		136.01				Property Tax							Property Tax
10	TaNet		3999				TaNet
11	Electricity		950				VZP
12	Cellar		300
13	Building Utilities		4027
14	Miscellaneous		2245.6
15	Income	30000
16	Miscellaneous
17	VZP		2187
18	VZP		2187
19	Miscellaneous		5072
20	Miscellaneous		220
21	Miscellaneous		136.01
22	Electricity		950
23	Cellar		300
24	Building Utilities		4027
25	VZP		2187
26	VZP		2187
27	Miscellaneous
28	Miscellaneous		220
29	Miscellaneous		136.01
30	Electricity		950
31	Cellar		300
32	Building Utilities		4027
33	Miscellaneous		4826.13
34	Miscellaneous		1269
Sheet1087

Cell Formulas
Range	Formula
D3:D34	D3	=IF(E3="",IFERROR(INDEX(Q$3:Q$9,MATCH(F3,S$2:S$8,0)),J$8),"Income")

 

[MrDB4Excel]

A few things.

1. You can Not change my INDEX/MATCH formula ranges as you did, the range sizes don't match, and will not produce correct results.
2. Your Column E and Column F formulas are putting a " " (space), in the cells that result TRUE, change that " " (space) to a nullstring ""
3. After you Fix point 2 above regarding the " " (space), you should be able to use this to check for Both Column E and Column F.

Book3.xlsx
	D	E	F				J							Q		S
1	Category	Income	Debits				1							Regular Monthly or Annual Expense		Amount
2	Miscellaneous	0					Income									2187
3	Income	50000					Building Utilities							VZP		300
4	VZP		2187				Cellar							Cellar		950
5	VZP		2187				CEZ Mobile Phone							Electricity		4027
6	Miscellaneous		53				Electricity							Building Utilities		3999
7	Miscellaneous		65				Foreign ATM Withdrawal							TaNet		356.01
8	Miscellaneous		220				Miscellaneous							CEZ Mobile Phone		1218
9	Miscellaneous		136.01				Property Tax							Property Tax
10	TaNet		3999				TaNet
11	Electricity		950				VZP
12	Cellar		300
13	Building Utilities		4027
14	Miscellaneous		2245.6
15	Income	30000
16	Miscellaneous
17	VZP		2187
18	VZP		2187
19	Miscellaneous		5072
20	Miscellaneous		220
21	Miscellaneous		136.01
22	Electricity		950
23	Cellar		300
24	Building Utilities		4027
25	VZP		2187
26	VZP		2187
27	Miscellaneous
28	Miscellaneous		220
29	Miscellaneous		136.01
30	Electricity		950
31	Cellar		300
32	Building Utilities		4027
33	Miscellaneous		4826.13
34	Miscellaneous		1269
Sheet1087

Cell Formulas
Range	Formula
D3:D34	D3	=IF(E3="",IFERROR(INDEX(Q$3:Q$9,MATCH(F3,S$2:S$8,0)),J$8),"Income")

=IF(E3="",IFERROR(INDEX(Q$3:Q$9,MATCH(F3,S$2:S$8,0)),J$8),"Income") changes all in column D to Income and not what is relevant to the numbers in column F.

 

[jtakw]

=IF(E3="",IFERROR(INDEX(Q$3:Q$9,MATCH(F3,S$2:S$8,0)),J$8),"Income") changes all in column D to Income and not what is relevant to the numbers in column F.

A few things.

1. You can Not change my INDEX/MATCH formula ranges as you did, the range sizes don't match, and will not produce correct results.
2. Your Column E and Column F formulas are putting a " " (space), in the cells that result TRUE, change that " " (space) to a nullstring ""
3. After you Fix point 2 above regarding the " " (space), you should be able to use this to check for Both Column E and Column F.

Did you change your Column E and Column F formulas from " " to "" ???

 

[MrDB4Excel]

Did you change your Column E and Column F formulas from " " to "" ???

I did not understand before. Now I do, changed all the " " to "" and now it all works superb. Awesome, thanks again jtakw

 

[jtakw]

You're welcome.