Lookup Amount Varies For Specific Category

[MrDB4Excel]

I have a worksheet that utilizes several functions to get data depending on other information.
One “Category” inputs Mobile Phone based on the amount of a given month’s expense.
This amount can vary as you can see by the attached Xl2bb.
Is there some way whenever the monthly Mobile expense differs from the norm that the Category indicates “Mobile Phone” in lieu of “Miscellaneous”?
In addition note the presence of the I$9 portion of the formula: =IF(E4=“”,IFERROR(INDEX(L$3:L$9,MATCH(F4,M$3:M$9,0)),I$9),”Income”) which occurs in all cells D4 thru D30. What is the significance or reason for this part of the formula? It seems redundant for no particular reason. Perhaps there is a better solution to this part of the formula? Also what part does the ,0 play in the portion: (F4,M$3:M$9,0)?

Bogus2EasyAccounting2022.xlsx
	A	B	C	D	E	F
1		Date	Transaction Description	Payment Amount	Deposit	Balance
2	Month	Date	Description	Debits	Income	Balance
3	1	Jan/01/2022	January Starting Balance			$5,000.00
4	1	Jan/06/2022	$ Cash deposit		$2,000.00	$7,000.00
5	1	Jan/06/2022	January 2022 Health Insurance	$100.00		$6,900.00
6	1	Jan/06/2022	January 2022 Health Insurance	$100.00		$6,800.00
7	1	Jan/14/2022	Electricity for January 2022	$110.00		$6,690.00
8	1	Jan/14/2022	Cellar Storage for January 2022	$50.00		$6,640.00
9	1	Jan/14/2022	Utilities for January 2022	$120.00		$6,520.00
10	2	Feb/09/2022	01-Mobile-Jan.pdf	$40.00		$6,480.00
11	2	Feb/07/2022	$ Cash deposit		$2,000.00	$8,480.00
12	2	Feb/06/2022	February 2022 Health Insurance	$100.00		$8,380.00
13	2	Feb/06/2022	February 2022 Health Insurance	$100.00		$8,280.00
14	2	Feb/14/2022	Electricity for February 2022	$110.00		$8,170.00
15		Feb/14/2022	Cellar Storage for February 2022	$50.00		$8,120.00
16	2	Feb/14/2022	Utilities for February 2022	$120.00		$8,000.00
17	3	Mar/08/2022	02-Mobile-Feb.pdf	$40.00		$7,960.00
18	3	Mar/07/2022	$ Cash deposit		$2,000.00	$9,960.00
19	3	Mar/06/2022	March 2022 Health Insurance	$100.00		$9,860.00
20	3	Mar/06/2022	March 2022 Health Insurance	$100.00		$9,760.00
21	3	Mar/14/2022	Electricity for March 2022	$110.00		$9,650.00
22	3	Mar/14/2022	Cellar Storage for March 2022	$50.00		$9,600.00
23	3	Mar/14/2022	Utilities for March 2022	$120.00		$9,480.00
24	4	Apr/08/2022	03-Mobile-Mar.pdf	$40.00		$9,440.00
25	4	Apr/07/2022	$ Cash deposit		$2,000.00	$11,440.00
26	4	Apr/07/2022	March 2022 Health Insurance	$100.00		$11,340.00
27	4	Apr/06/2022	March 2022 Health Insurance	$100.00		$11,240.00
28	4	Apr/14/2022	Electricity for March 2022	$110.00		$11,130.00
29	4	Apr/14/2022	Cellar Storage for March 2022	$50.00		$11,080.00
30	4	Apr/14/2022	Utilities for March 2022	$120.00		$10,960.00
Expenses

Cell Formulas
Range	Formula
A16:A30,A3:A14	A3	=MONTH(B3)
F4:F30	F4	=SUM(F3+[@Income]-[@Debits])

Bogus2AutomatedMonthlyBudget.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Date	Transaction Description		Deposit	Payment Amount	Balance
2	Month	Date	Description	Category	Income	Debits	Balance		1			Regular Monthly or Annual Expense	Amount
3	1	Jan/01/2022	January Starting Balance	Miscellaneous	$0.00		$5,000.00		Income	Jan/01/2022	January	AARP	100.00	R2
4	1	Jan/06/2022	$ Cash deposit	Income	$2,000.00		$7,000.00		AARP	Feb/01/2022	February	Bsmt Stge	50.00	R3
5	1	Jan/06/2022	January 2022 Health Insurance	AARP		$100.00	$6,900.00		Electricity	Mar/01/2022	March	Electricity	110.00	R4
6	1	Jan/06/2022	January 2022 Health Insurance	AARP		$100.00	$6,800.00		Bsmt Stge	Apr/01/2022	April	Building Utilities	120.00	R5
7	1	Jan/14/2022	Electricity for January 2022	Electricity		$110.00	$6,690.00		Building Utilities	May/01/2022	May	Monthly Internet	80.00	R6
8	1	Jan/14/2022	Cellar Storage for January 2022	Bsmt Stge		$50.00	$6,640.00		Mobile Phone	Jun/01/2022	June	Mobile Phone	40.00	R7
9	1	Jan/14/2022	Utilities for January 2022	Building Utilities		$120.00	$6,520.00		Miscellaneous	Jul/01/2022	July	Income	2000.00	R8
10	2	Feb/09/2022	01-Mobile-Jan.pdf	Mobile Phone		$40.00	$6,480.00		Monthly Internet	Aug/01/2022	August			R9
11	2	Feb/07/2022	$ Cash deposit	Income	$2,000.00		$8,480.00		Property Tax	Sep/01/2022	September			R10
12	2	Feb/06/2022	February 2022 Health Insurance	AARP		$100.00	$8,380.00			Oct/01/2022	October			R11
13	2	Feb/06/2022	February 2022 Health Insurance	AARP		$100.00	$8,280.00			Nov/01/2022	November			R12
14	2	Feb/14/2022	Electricity for February 2022	Electricity		$110.00	$8,170.00			Dec/01/2022	December			R13
15	2	Feb/14/2022	Cellar Storage for February 2022	Bsmt Stge		$50.00	$8,120.00							R14
16	2	Feb/14/2022	Utilities for February 2022	Building Utilities		$120.00	$8,000.00
17	3	Mar/08/2022	02-Mobile-Feb.pdf	Mobile Phone		$40.00	$7,960.00
18	3	Mar/07/2022	$ Cash deposit	Income	$2,000.00		$9,960.00
19	3	Mar/06/2022	March 2022 Health Insurance	AARP		$100.00	$9,860.00
20	3	Mar/06/2022	March 2022 Health Insurance	AARP		$100.00	$9,760.00
21	3	Mar/14/2022	Electricity for March 2022	Electricity		$110.00	$9,650.00
22	3	Mar/14/2022	Cellar Storage for March 2022	Bsmt Stge		$50.00	$9,600.00
23	3	Mar/14/2022	Utilities for March 2022	Building Utilities		$120.00	$9,480.00
24	4	Apr/08/2022	03-Mobile-Mar.pdf	Miscellaneous		$35.00	$9,445.00
25	4	Apr/07/2022	$ Cash deposit	Income	$2,000.00		$11,445.00
26	4	Apr/07/2022	March 2022 Health Insurance	AARP		$100.00	$11,345.00
27	4	Apr/06/2022	March 2022 Health Insurance	AARP		$100.00	$11,245.00
28	4	Apr/14/2022	Electricity for March 2022	Electricity		$110.00	$11,135.00
29	4	Apr/14/2022	Cellar Storage for March 2022	Bsmt Stge		$50.00	$11,085.00
30	4	Apr/14/2022	Utilities for March 2022	Building Utilities		$120.00	$10,965.00
2022

Cell Formulas
Range	Formula
A3:A30	A3	=IF((ISERROR(MONTH('2022'!$B3)))," ",(MONTH('2022'!$B3)))
B3:B30	B3	=IF(ISBLANK([Bogus2EasyAccounting2022.xlsx]Expenses!$B3)," ",([Bogus2EasyAccounting2022.xlsx]Expenses!$B3))
C3:C30	C3	=IF(ISBLANK([Bogus2EasyAccounting2022.xlsx]Expenses!$C3)," ",([Bogus2EasyAccounting2022.xlsx]Expenses!$C3))
D4:D30	D4	=IF(E4="",IFERROR(INDEX(L$3:L$9,MATCH(F4,M$3:M$9,0)),I$9),"Income")
E4:E30	E4	=IF(ISBLANK([Bogus2EasyAccounting2022.xlsx]Expenses!$E4),"",([Bogus2EasyAccounting2022.xlsx]Expenses!$E4))
F25:F30,F5:F23	F5	=IF(ISBLANK([Bogus2EasyAccounting2022.xlsx]Expenses!$D5),"",([Bogus2EasyAccounting2022.xlsx]Expenses!$D5))
G4	G4	=SUM(G3+[@Income]-[@Debits])
G5:G30	G5	=G4+N([@Income])-N([@Debits])

 

[MrDB4Excel]

I have continued looking for an answer and have thus far found nothing that helps. I would so much appreciate any suggestions, thanks.

 

[Alex Blakenburg]

I find it helpful to lay out the formula this way.

=IF(E4="",
       IFERROR(
                INDEX(L$3:L$9,MATCH(F4,M$3:M$9,0)),
                I$9),
        "Income")

Is there some way whenever the monthly Mobile expense differs from the norm that the Category indicates “Mobile Phone” in lieu of “Miscellaneous”?

At the moment if it can't find a Match for the amount (F4), it will cause and error and return I$9 which is Miscellaneous, Change it to I$8 if you want Mobile phone.
I assume can't find the amount equates to "differs from the norm"

In addition note the presence of the I$9 portion of the formula: =IF(E4=“”,IFERROR(INDEX(L$3:L$9,MATCH(F4,M$3:M$9,0)),I$9),”Income”) which occurs in all cells D4 thru D30. What is the significance or reason for this part of the formula? It seems redundant for no particular reason. Perhaps there is a better solution to this part of the formula?

See above

Also what part does the ,0 play in the portion: (F4,M$3:M$9,0)?

the 0 tells the Match that is to look for an exact match and not the nearest below or above

 

[MrDB4Excel]

I find it helpful to lay out the formula this way.

=IF(E4="",
       IFERROR(
                INDEX(L$3:L$9,MATCH(F4,M$3:M$9,0)),
                I$9),
        "Income")

At the moment if it can't find a Match for the amount (F4), it will cause and error and return I$9 which is Miscellaneous, Change it to I$8 if you want Mobile phone.
I assume can't find the amount equates to "differs from the norm"

See above

the 0 tells the Match that is to look for an exact match and not the nearest below or above

I laid out the formula as you indicate and all seems to work okay but I am puzzled as to what the significance is of the I$9 or I$8 or whatever number goes with I$
So I changed the I$8 or 9 to I$3:I$11 and all seems to work okay.

 

[Alex Blakenburg]

The I8 or I9 is being used to retrieve an item from the table when the match errors out.
In this usage I probably would have hard coded it eg "Mobile Phone"