Insert Value Based on Multiple Criteria

MrDB4Excel

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
348
Office Version
  1. 2013
Platform
  1. Windows
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
ABCDEF
2MonthDateDescriptionDebitsIncomeBalance
31Jan/06/2022January Bogus Starting Balance20,000.00 Kč
41Jan/06/2022Kč Cash deposit20,000.00 Kč40,000.00 Kč
51Jan/06/2022January 2021 VZP2,187.00 Kč37,813.00 Kč
61Jan/06/2022January 2021 VZP2,187.00 Kč35,626.00 Kč
71Jan/07/20221693 www.cd.cz/eshop/ ML2Praha53.00 Kč35,573.00 Kč
81Jan/07/20221693 www.cd.cz/eshop/ Praha2ML65.00 Kč35,508.00 Kč
91Jan/08/2022December Mobile Service220.00 Kč35,288.00 Kč
101Jan/08/2022Bogus Miscellaneous136.01 Kč35,151.99 Kč
111Jan/14/2022Annual Internet3,999.00 Kč31,152.99 Kč
121Jan/14/2022Electricity for January 2022950.00 Kč30,202.99 Kč
131Jan/14/2022Cellar Storage for January 2022300.00 Kč29,902.99 Kč
141Jan/14/2022Utilities for January 20224,027.00 Kč25,875.99 Kč
151Jan/28/2022US EMBASSY PRAGUE2,245.60 Kč23,630.39 Kč
162Feb/03/2022Bogus Miscellaneous3,525.53 Kč20,104.86 Kč
172Feb/08/2022Kč Cash deposit20,000.00 Kč40,104.86 Kč
182Feb/06/2022January 2021 VZP2,187.00 Kč37,917.86 Kč
192Feb/06/2022January 2021 VZP2,187.00 Kč35,730.86 Kč
202Feb/07/2022Bogus Miscellaneous5,072.00 Kč30,658.86 Kč
212Feb/09/2022January Mobile Service220.00 Kč30,438.86 Kč
222Feb/09/2022Bogus Miscellaneous136.01 Kč30,302.85 Kč
232Feb/14/2022Electricity for February 2022950.00 Kč29,352.85 Kč
242Feb/14/2022Cellar Storage for February 2022300.00 Kč29,052.85 Kč
252Feb/14/2022Utilities for February 20224,027.00 Kč25,025.85 Kč
263Mar/06/2022March 2022 VZP2,187.00 Kč22,838.85 Kč
273Mar/06/2022March 2022 VZP2,187.00 Kč20,651.85 Kč
283Mar/07/2022Kč Cash deposit20,000.00 Kč40,651.85 Kč
293Mar/08/2022February Mobile Service220.00 Kč40,431.85 Kč
303Mar/11/2022Bogus Miscellaneous136.01 Kč40,295.84 Kč
313Mar/14/2022Electricity for March 2022950.00 Kč39,345.84 Kč
323Mar/14/2022Cellar Storage for March 2022300.00 Kč39,045.84 Kč
333Mar/14/2022Utilities for March 20224,027.00 Kč35,018.84 Kč
343Mar/17/2022Bogus Miscellaneous4,826.13 Kč30,192.71 Kč
353Mar/27/2022Bogus Miscellaneous1,269.00 Kč28,923.71 Kč
36128,923.71 Kč
37128,923.71 Kč
Expenses
Cell Formulas
RangeFormula
A3:A37A3=MONTH(B3)
F4:F37F4=SUM(F3+[@Income]-[@Debits])


BogusAutomatedMonthlyBudget.xlsx
ABCDEFGHIJKLMNOPQR
1MonthDateDescriptionCategoryIncomeDebitsBalance1Running TotalsTotalsRegular Monthly or Annual ExpenseNon-Regular Expense
21Jan/06/2022January Bogus Starting BalanceMiscellaneous0.00 Kč20,000.00 Kč1JanuaryIncome60,000.00 Kč20,000.00 Kč1JanuaryN20.00 Kč0.00 Kč
31Jan/06/2022Kč Cash depositIncome20,000.00 Kč40,000.00 Kč1Building Utilities12,081.00 Kč4,027.00 Kč2FebruaryN3VZP2,187.00 Kč
41Jan/06/2022January 2021 VZPVZP 2,187.00 Kč37,813.00 Kč1Cellar900.00 Kč300.00 Kč3MarchN4Cellar300.00 Kč
51Jan/06/2022January 2021 VZPVZP 2,187.00 Kč35,626.00 Kč1CEZ Mobile Phone660.00 Kč220.00 Kč4AprilN5Electricity950.00 Kč
61Jan/07/20221693 www.cd.cz/eshop/ ML2PrahaMiscellaneous 53.00 Kč35,573.00 Kč1Electricity2,850.00 Kč950.00 Kč5MayN6Building Utilities4,027.00 Kč
71Jan/07/20221693 www.cd.cz/eshop/ Praha2MLMiscellaneous 65.00 Kč35,508.00 Kč1Foreign ATM Withdrawal0.00 Kč0.00 Kč6JuneN7TaNet3,999.00 Kč
81Jan/08/2022December Mobile ServiceCEZ Mobile Phone 220.00 Kč35,288.00 Kč1Miscellaneous17,464.29 Kč2,499.61 Kč7JulyN8CEZ Mobile Phone356.01 Kč
91Jan/08/2022Bogus MiscellaneousMiscellaneous 136.01 Kč35,151.99 Kč1Property Tax0.00 Kč0.00 Kč8AugustN9Property Tax1,218.00 Kč
101Jan/14/2022Annual InternetTaNet 3,999.00 Kč31,152.99 Kč1TaNet3,999.00 Kč3,999.00 Kč9SeptemberN10
111Jan/14/2022Electricity for January 2022Electricity 950.00 Kč30,202.99 Kč1VZP13,122.00 Kč4,374.00 Kč10OctoberN11
121Jan/14/2022Cellar Storage for January 2022Cellar 300.00 Kč29,902.99 Kč111NovemberN12
131Jan/14/2022Utilities for January 2022Building Utilities 4,027.00 Kč25,875.99 Kč1SubTotal Debits51,076.29 Kč12DecemberN13
141Jan/28/2022US EMBASSY PRAGUEMiscellaneous 2,245.60 Kč23,630.39 Kč1
152Feb/03/2022Bogus MiscellaneousMiscellaneous 3,525.53 Kč20,104.86 Kč2#VALUE! 2021 Mobile Phone8,923.71 Kč
162Feb/08/2022Kč Cash depositIncome20,000.00 Kč 40,104.86 Kč2
172Feb/06/2022January 2021 VZPVZP 2,187.00 Kč37,917.86 Kč228,923.71 Kč
182Feb/06/2022January 2021 VZPVZP 2,187.00 Kč35,730.86 Kč2
192Feb/07/2022Bogus MiscellaneousMiscellaneous 5,072.00 Kč30,658.86 Kč2#VALUE!
202Feb/09/2022January Mobile ServiceCEZ Mobile Phone 220.00 Kč30,438.86 Kč22,187.00 Kč
212Feb/09/2022Bogus MiscellaneousMiscellaneous 136.01 Kč30,302.85 Kč2300.00 Kč
222Feb/14/2022Electricity for February 2022Electricity 950.00 Kč29,352.85 Kč2950.00 Kč
232Feb/14/2022Cellar Storage for February 2022Cellar 300.00 Kč29,052.85 Kč24,027.00 Kč
242Feb/14/2022Utilities for February 2022Building Utilities 4,027.00 Kč25,025.85 Kč23,999.00 Kč
253Mar/06/2022March 2022 VZPVZP 2,187.00 Kč22,838.85 Kč3356.01 Kč
263Mar/06/2022March 2022 VZPVZP 2,187.00 Kč20,651.85 Kč31,218.00 Kč
273Mar/07/2022Kč Cash depositIncome20,000.00 Kč 40,651.85 Kč3January1
283Mar/08/2022February Mobile ServiceCEZ Mobile Phone 220.00 Kč40,431.85 Kč3Electricity#VALUE!21
293Mar/11/2022Bogus MiscellaneousMiscellaneous 136.01 Kč40,295.84 Kč3#N/A#VALUE!31
303Mar/14/2022Electricity for March 2022Electricity 950.00 Kč39,345.84 Kč3#VALUE!#N/A41
313Mar/14/2022Cellar Storage for March 2022Cellar 300.00 Kč39,045.84 Kč3#N/A51
323Mar/14/2022Utilities for March 2022Building Utilities 4,027.00 Kč35,018.84 Kč3#N/A61
333Mar/17/2022Bogus MiscellaneousMiscellaneous 4,826.13 Kč30,192.71 Kč3#N/A71
343Mar/27/2022Bogus MiscellaneousMiscellaneous 1,269.00 Kč28,923.71 Kč3#N/A81
35 28,923.71 Kč #N/A91
36 28,923.71 Kč #N/A101
2022
Cell Formulas
RangeFormula
A2:A36A2=IF((ISERROR(MONTH('2022'!$B2)))," ",(MONTH('2022'!$B2)))
B2:B36B2=IF(ISBLANK([BogusEasyAccounting2022.xlsx]Expenses!$B3)," ",([BogusEasyAccounting2022.xlsx]Expenses!$B3))
C2:C36C2=IF(ISBLANK([BogusEasyAccounting2022.xlsx]Expenses!$C3)," ",([BogusEasyAccounting2022.xlsx]Expenses!$C3))
G2G2=[BogusEasyAccounting2022.xlsx]Expenses!$F$3
H2:H36H2=IF((ISERROR(VALUE(A2)))," ",(VALUE(A2)))
I2I2=XLOOKUP(M2,$A$2:$A$36,$N$2:$N$13,0)
K2K2=SUMIF(D:D,J2,E:E)
L2L2=SUMIFS(Table23[Income],Table23[Month],$J$1,Table23[Category],J2)
K3:K11K3=SUMIF(D:D,J3,F:F)
L3:L11L3=SUMIFS(Table23[Debits],Table23[Month],$J$1,Table23[Category],J3)
Q2Q2=SUMPRODUCT(--(F:F=N16),D:D)
R2R2=SUMPRODUCT(--(E:E=N17),D:D)
E3E3=[BogusEasyAccounting2022.xlsx]Expenses!$E$4
G3G3=SUM(G2+[@Income]-[@Debits])
E4:E36E4=IF(ISBLANK([BogusEasyAccounting2022.xlsx]Expenses!$E5)," ",([BogusEasyAccounting2022.xlsx]Expenses!$E5))
F4:F36F4=IF(ISBLANK([BogusEasyAccounting2022.xlsx]Expenses!$D5)," ",([BogusEasyAccounting2022.xlsx]Expenses!$D5))
G4:G36G4=G3+N([@Income])-N([@Debits])
K13K13=SUM(K3:K11)
I15I15=XLOOKUP(M3,$A$2:$A$36,$N$2:$N$13,0)
K15K15=SUM(K2-K13)
J17J17=LOOKUP(2,1/(G:G<>""),G:G)
J19J19=IF(ISNUMBER(MATCH(F4,$R$3:$R$9,0)),Q2:Q11)
J20:J26J20=SUMPRODUCT(($Q$3:$Q$9=Q3)*($R$3:$R$9=R3)*($R$3:$R$9))
J28J28=LOOKUP(F4,R3:R9,Q3:Q9)
J29J29=XLOOKUP(F4,Q3:Q9&R3:R9,0)
I30I30=XLOOKUP(M4,$H$2:$H$36,$N$2:$N$13,0)
L27:L36L27=XLOOKUP(M2,$H$2:$H$36,$N$2:$N$13,0)
N28:N36N28=IF((ISERROR(VALUE(A5)))," ",(VALUE(A5)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L2Other TypeDataBarNO
Cells with Data Validation
CellAllowCriteria
D1ListSSB-VZP, DDB-VZP, Dvorakova 533/2 Misc, CEZ Mobile Phone, Income, Product, Miscellaneous, CSOB Account Charges
D2:D36ListBuilding Utilities, Cellar, CEZ Mobile Phone, Electricity, Foreign ATM Withdrawal, Income, Miscellaneous, Property Tax, TaNet, VZP
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
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
ABCDEFGHIJKLMNOPQRS
1MonthDateDescriptionCategoryIncomeDebitsBalance1Running TotalsTotalsRegular Monthly or Annual ExpenseNon-Regular ExpenseAmount
21Jan/06/2022January Bogus Starting BalanceMiscellaneous0.00 Kč20,000.00 Kč1JanuaryIncome60,000.00 Kč20,000.00 Kč1JanuaryN20.00 Kč0.00 Kč2187.00
31Jan/06/2022Kč Cash depositIncome20,000.00 Kč40,000.00 Kč1Building Utilities12,081.00 Kč4,027.00 Kč2FebruaryN3VZP2,187.00 Kč300.00
41Jan/06/2022January 2021 VZPVZP 2,187.00 Kč37,813.00 Kč1Cellar900.00 Kč300.00 Kč3MarchN4Cellar300.00 Kč950.00
51Jan/06/2022January 2021 VZPVZP 2,187.00 Kč35,626.00 Kč1CEZ Mobile Phone660.00 Kč220.00 Kč4AprilN5Electricity950.00 Kč4027.00
61Jan/07/20221693 www.cd.cz/eshop/ ML2PrahaMiscellaneous 53.00 Kč35,573.00 Kč1Electricity2,850.00 Kč950.00 Kč5MayN6Building Utilities4,027.00 Kč3999.00
71Jan/07/20221693 www.cd.cz/eshop/ Praha2MLMiscellaneous 65.00 Kč35,508.00 Kč1Foreign ATM Withdrawal0.00 Kč0.00 Kč6JuneN7TaNet3,999.00 Kč356.01
81Jan/08/2022December Mobile ServiceCEZ Mobile Phone 220.00 Kč35,288.00 Kč1Miscellaneous17,464.29 Kč2,499.61 Kč7JulyN8CEZ Mobile Phone356.01 Kč1218.00
91Jan/08/2022Bogus MiscellaneousMiscellaneous 136.01 Kč35,151.99 Kč1Property Tax0.00 Kč0.00 Kč8AugustN9Property Tax1,218.00 Kč
101Jan/14/2022Annual InternetTaNet 3,999.00 Kč31,152.99 Kč1TaNet3,999.00 Kč3,999.00 Kč9SeptemberN10
111Jan/14/2022Electricity for January 2022Electricity 950.00 Kč30,202.99 Kč1VZP13,122.00 Kč4,374.00 Kč10OctoberN11
121Jan/14/2022Cellar Storage for January 2022Cellar 300.00 Kč29,902.99 Kč111NovemberN12
131Jan/14/2022Utilities for January 2022Building Utilities 4,027.00 Kč25,875.99 Kč1SubTotal Debits51,076.29 Kč12DecemberN13
141Jan/28/2022US EMBASSY PRAGUEMiscellaneous 2,245.60 Kč23,630.39 Kč1
152Feb/03/2022Bogus MiscellaneousMiscellaneous 3,525.53 Kč20,104.86 Kč2#VALUE! 2021 Mobile Phone8,923.71 Kč
162Feb/08/2022Kč Cash depositIncome20,000.00 Kč 40,104.86 Kč2
172Feb/06/2022January 2021 VZPVZP 2,187.00 Kč37,917.86 Kč228,923.71 Kč
182Feb/06/2022January 2021 VZPVZP 2,187.00 Kč35,730.86 Kč2#N/A
192Feb/07/2022Bogus MiscellaneousMiscellaneous 5,072.00 Kč30,658.86 Kč2#VALUE!
202Feb/09/2022January Mobile ServiceCEZ Mobile Phone 220.00 Kč30,438.86 Kč22,187.00 Kč
212Feb/09/2022Bogus MiscellaneousMiscellaneous 136.01 Kč30,302.85 Kč2300.00 Kč
222Feb/14/2022Electricity for February 2022Electricity 950.00 Kč29,352.85 Kč2950.00 Kč
232Feb/14/2022Cellar Storage for February 2022Cellar 300.00 Kč29,052.85 Kč24,027.00 Kč
242Feb/14/2022Utilities for February 2022Building Utilities 4,027.00 Kč25,025.85 Kč23,999.00 Kč
253Mar/06/2022March 2022 VZPVZP 2,187.00 Kč22,838.85 Kč3356.01 Kč
263Mar/06/2022March 2022 VZPVZP 2,187.00 Kč20,651.85 Kč31,218.00 Kč
273Mar/07/2022Kč Cash depositIncome20,000.00 Kč 40,651.85 Kč3January1
283Mar/08/2022February Mobile ServiceCEZ Mobile Phone 220.00 Kč40,431.85 Kč3Electricity#VALUE!21
293Mar/11/2022Bogus MiscellaneousMiscellaneous 136.01 Kč40,295.84 Kč3#N/A#VALUE!31
303Mar/14/2022Electricity for March 2022Electricity 950.00 Kč39,345.84 Kč3#VALUE!#VALUE!#N/A41
313Mar/14/2022Cellar Storage for March 2022Cellar 300.00 Kč39,045.84 Kč3#VALUE!#N/A51
323Mar/14/2022Utilities for March 2022Building Utilities 4,027.00 Kč35,018.84 Kč3#N/A61
333Mar/17/2022Bogus MiscellaneousMiscellaneous 4,826.13 Kč30,192.71 Kč3#N/A71
343Mar/27/2022Bogus MiscellaneousMiscellaneous 1,269.00 Kč28,923.71 Kč3#N/A81
35 28,923.71 Kč #N/A91
36 28,923.71 Kč #N/A101
2022
Cell Formulas
RangeFormula
A2:A36A2=IF((ISERROR(MONTH('2022'!$B2)))," ",(MONTH('2022'!$B2)))
B2:B36B2=IF(ISBLANK('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$B3)," ",('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$B3))
C2:C36C2=IF(ISBLANK('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$C3)," ",('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$C3))
G2G2='F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$F$3
H2:H36H2=IF((ISERROR(VALUE(A2)))," ",(VALUE(A2)))
I2I2=XLOOKUP(M2,$A$2:$A$36,$N$2:$N$13,0)
K2K2=SUMIF(D:D,J2,E:E)
L2L2=SUMIFS(Table23[Income],Table23[Month],$J$1,Table23[Category],J2)
K3:K11K3=SUMIF(D:D,J3,F:F)
L3:L11L3=SUMIFS(Table23[Debits],Table23[Month],$J$1,Table23[Category],J3)
Q2Q2=SUMPRODUCT(--(F:F=N16),D:D)
R2R2=SUMPRODUCT(--(E:E=N17),D:D)
E3E3='F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$E$4
G3G3=SUM(G2+[@Income]-[@Debits])
D4D4=XLOOKUP(R3,$F$4:$F$34,$Q$3:$Q$9,0)
E4:E36E4=IF(ISBLANK('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$E5)," ",('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$E5))
F4:F36F4=IF(ISBLANK('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$D5)," ",('F:\Finances\CSOB\[BogusEasyAccounting2022.xlsx]Expenses'!$D5))
G4:G36G4=G3+N([@Income])-N([@Debits])
K13K13=SUM(K3:K11)
I15I15=XLOOKUP(M3,$A$2:$A$36,$N$2:$N$13,0)
K15K15=SUM(K2-K13)
J17J17=LOOKUP(2,1/(G:G<>""),G:G)
L18L18=XLOOKUP(Q3&R3,$D$2:$D$34&$R$3:$R$9,F4:F34)
J19J19=IF(ISNUMBER(MATCH(F4,$R$3:$R$9,0)),Q2:Q11)
J20:J26J20=SUMPRODUCT(($Q$3:$Q$9=Q3)*($R$3:$R$9=R3)*($R$3:$R$9))
I30I30=XLOOKUP(M4,$H$2:$H$36,$N$2:$N$13,0)
J28J28=LOOKUP(F4,R3:R9,Q3:Q9)
J29J29=XLOOKUP(F4,Q3:Q9&R3:R9,0)
J30J30=XLOOKUP(R3:R9,$F$4:$F$34,$Q$3:$Q$9,0)
J31J31=XLOOKUP(R4,$F$4:$F$34,$Q$3:$Q$9,0)
L27:L36L27=XLOOKUP(M2,$H$2:$H$36,$N$2:$N$13,0)
N28:N36N28=IF((ISERROR(VALUE(A5)))," ",(VALUE(A5)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L2Other TypeDataBarNO
Cells with Data Validation
CellAllowCriteria
D1ListSSB-VZP, DDB-VZP, Dvorakova 533/2 Misc, CEZ Mobile Phone, Income, Product, Miscellaneous, CSOB Account Charges
 
Upvote 0
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.
 
Upvote 0
Hi,

Try this in D4 copied down:

Book3.xlsx
DFJQS
1CategoryDebits1Regular Monthly or Annual ExpenseAmount
2MiscellaneousIncome2187
3IncomeBuilding UtilitiesVZP300
4VZP2187CellarCellar950
5VZP2187CEZ Mobile PhoneElectricity4027
6Miscellaneous53ElectricityBuilding Utilities3999
7Miscellaneous65Foreign ATM WithdrawalTaNet356.01
8Miscellaneous220MiscellaneousCEZ Mobile Phone1218
9Miscellaneous136.01Property TaxProperty Tax
10TaNet3999TaNet
11Electricity950VZP
12Cellar300
13Building Utilities4027
14Miscellaneous2245.6
15Miscellaneous3525.53
16Miscellaneous
17VZP2187
18VZP2187
19Miscellaneous5072
20Miscellaneous220
21Miscellaneous136.01
22Electricity950
23Cellar300
24Building Utilities4027
25VZP2187
26VZP2187
27Miscellaneous
28Miscellaneous220
29Miscellaneous136.01
30Electricity950
31Cellar300
32Building Utilities4027
33Miscellaneous4826.13
34Miscellaneous1269
Sheet1087
Cell Formulas
RangeFormula
D4:D34D4=IFERROR(INDEX(Q$3:Q$9,MATCH(F4,S$2:S$8,0)),J$8)
 
Upvote 0
jtakw said:
Hi,

Try this in D4 copied down:

Book3.xlsx
DFJQS
1CategoryDebits1Regular Monthly or Annual ExpenseAmount
2MiscellaneousIncome2187
3IncomeBuilding UtilitiesVZP300
4VZP2187CellarCellar950
5VZP2187CEZ Mobile PhoneElectricity4027
6Miscellaneous53ElectricityBuilding Utilities3999
7Miscellaneous65Foreign ATM WithdrawalTaNet356.01
8Miscellaneous220MiscellaneousCEZ Mobile Phone1218
9Miscellaneous136.01Property TaxProperty Tax
10TaNet3999TaNet
11Electricity950VZP
12Cellar300
13Building Utilities4027
14Miscellaneous2245.6
15Miscellaneous3525.53
16Miscellaneous
17VZP2187
18VZP2187
19Miscellaneous5072
20Miscellaneous220
21Miscellaneous136.01
22Electricity950
23Cellar300
24Building Utilities4027
25VZP2187
26VZP2187
27Miscellaneous
28Miscellaneous220
29Miscellaneous136.01
30Electricity950
31Cellar300
32Building Utilities4027
33Miscellaneous4826.13
34Miscellaneous1269
Sheet1087
Cell Formulas
RangeFormula
D4:D34D4=IFERROR(INDEX(Q$3:Q$9,MATCH(F4,S$2:S$8,0)),J$8)
Click to expand...


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
DEFGJQRS
1CategoryIncomeDebitsBalance1Regular Monthly or Annual ExpenseNon-Regular ExpenseAmount
2Miscellaneous0.00 Kč54,566.97 KčIncome0.00 Kč0.00 Kč2187.00
3Income50,000.00 Kč104,566.97 KčBuilding UtilitiesVZP2,187.00 Kč300.00
4VZP 2,187.00 Kč102,379.97 KčCellarCellar300.00 Kč950.00
5VZP 2,187.00 Kč100,192.97 KčCEZ Mobile PhoneElectricity950.00 Kč4027.00
6Miscellaneous 53.00 Kč100,139.97 KčElectricityBuilding Utilities4,027.00 Kč3999.00
7Miscellaneous 65.00 Kč100,074.97 KčForeign ATM WithdrawalTaNet3,999.00 Kč356.01
8CEZ Mobile Phone 356.01 Kč99,718.96 KčMiscellaneousCEZ Mobile Phone356.01 Kč1218.00
9TaNet 3,999.00 Kč95,719.96 KčProperty TaxProperty Tax1,218.00 Kč10000.00
10Electricity 950.00 Kč94,769.96 KčTaNetIncome20000.00
11Cellar 300.00 Kč94,469.96 KčVZP30000.00
12Building Utilities 4,027.00 Kč90,442.96 Kč40000.00
13Miscellaneous 2,245.60 Kč88,197.36 KčSubTotal Debits50000.00
14Miscellaneous 3,525.53 Kč84,671.83 Kč
15Miscellaneous30,000.00 Kč 114,671.83 Kč 2021 Mobile Phone
16VZP 2,187.00 Kč112,484.83 Kč
17VZP 2,187.00 Kč110,297.83 Kč44,116.68 Kč
2022
Cell Formulas
RangeFormula
G2G2='2021'!J17
Q2Q2=SUMPRODUCT(--(F:F=N16),D:D)
R2R2=SUMPRODUCT(--(E:E=N17),D:D)
E3E3=AI4
G3G3=SUM(G2+[@Income]-[@Debits])
D4:D17D4=IFERROR(INDEX(Q$3:Q$10,MATCH(F4,S$2:S$13,0)),J$8)
E4:E17E4=IF(ISBLANK('F:\Finances\CSOB\[EasyAccounting2022.xlsx]Expenses'!$E5)," ",('F:\Finances\CSOB\[EasyAccounting2022.xlsx]Expenses'!$E5))
F4:F17F4=IF(ISBLANK('F:\Finances\CSOB\[EasyAccounting2022.xlsx]Expenses'!$D5)," ",('F:\Finances\CSOB\[EasyAccounting2022.xlsx]Expenses'!$D5))
G4:G17G4=G3+N([@Income])-N([@Debits])
J17J17=LOOKUP(2,1/(G:G<>""),G:G)
 
Upvote 0
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
DEFJQS
1CategoryIncomeDebits1Regular Monthly or Annual ExpenseAmount
2Miscellaneous0Income2187
3Income50000Building UtilitiesVZP300
4VZP2187CellarCellar950
5VZP2187CEZ Mobile PhoneElectricity4027
6Miscellaneous53ElectricityBuilding Utilities3999
7Miscellaneous65Foreign ATM WithdrawalTaNet356.01
8Miscellaneous220MiscellaneousCEZ Mobile Phone1218
9Miscellaneous136.01Property TaxProperty Tax
10TaNet3999TaNet
11Electricity950VZP
12Cellar300
13Building Utilities4027
14Miscellaneous2245.6
15Income30000
16Miscellaneous
17VZP2187
18VZP2187
19Miscellaneous5072
20Miscellaneous220
21Miscellaneous136.01
22Electricity950
23Cellar300
24Building Utilities4027
25VZP2187
26VZP2187
27Miscellaneous
28Miscellaneous220
29Miscellaneous136.01
30Electricity950
31Cellar300
32Building Utilities4027
33Miscellaneous4826.13
34Miscellaneous1269
Sheet1087
Cell Formulas
RangeFormula
D3:D34D3=IF(E3="",IFERROR(INDEX(Q$3:Q$9,MATCH(F3,S$2:S$8,0)),J$8),"Income")
 
Upvote 0
jtakw said:
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
DEFJQS
1CategoryIncomeDebits1Regular Monthly or Annual ExpenseAmount
2Miscellaneous0Income2187
3Income50000Building UtilitiesVZP300
4VZP2187CellarCellar950
5VZP2187CEZ Mobile PhoneElectricity4027
6Miscellaneous53ElectricityBuilding Utilities3999
7Miscellaneous65Foreign ATM WithdrawalTaNet356.01
8Miscellaneous220MiscellaneousCEZ Mobile Phone1218
9Miscellaneous136.01Property TaxProperty Tax
10TaNet3999TaNet
11Electricity950VZP
12Cellar300
13Building Utilities4027
14Miscellaneous2245.6
15Income30000
16Miscellaneous
17VZP2187
18VZP2187
19Miscellaneous5072
20Miscellaneous220
21Miscellaneous136.01
22Electricity950
23Cellar300
24Building Utilities4027
25VZP2187
26VZP2187
27Miscellaneous
28Miscellaneous220
29Miscellaneous136.01
30Electricity950
31Cellar300
32Building Utilities4027
33Miscellaneous4826.13
34Miscellaneous1269
Sheet1087
Cell Formulas
RangeFormula
D3:D34D3=IF(E3="",IFERROR(INDEX(Q$3:Q$9,MATCH(F3,S$2:S$8,0)),J$8),"Income")
Click to expand...

=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.
 
Upvote 0
MrDB4Excel said:
=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.
Click to expand...

jtakw said:
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.
Click to expand...

Did you change your Column E and Column F formulas from " " to "" ???
 
Upvote 0
jtakw said:
Did you change your Column E and Column F formulas from " " to "" ???
Click to expand...
I did not understand before. Now I do, changed all the " " to "" and now it all works superb. Awesome, thanks again jtakw
 
Upvote 0
You must log in or register to reply here.

Similar threads

Romano_odK
  • Question Question
How to get average marge for every months in the current year.
Replies
3
Views
370
James006
James006
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
934
paulkc
P
MrDB4Excel
  • Question Question
Lookup Amount Varies For Specific Category
Replies
4
Views
561
Alex Blakenburg
A
MrDB4Excel
  • Solved
XLOOKUP Not Working
Replies
2
Views
2K
MrDB4Excel
MrDB4Excel
MrDB4Excel
  • Solved
Formula Not Working
Replies
6
Views
955
MrDB4Excel
MrDB4Excel

Forum statistics

Threads
1,224,814
Messages
6,181,123
Members
453,021
Latest member
Justyna P

Share this page

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