Categorizing Formula

[Austin Lang]

Hey all,

I am working on a budgeting sheet that I created to get a hold on my finances. I created a formula that will auto-categorize my transactions when I paste in my transactions. Here is that formula..

=IF(ISBLANK(M5),"",IFERROR(INDEX($V$50:$V$153,MATCH(TRUE,ISNUMBER(SEARCH($U$50:$U$153,$M5)),0)),"Unknown"))

Here are some snips of the transactions and some assigned categories..

When the categories table grows, I have the extend the range in the above formula to look at the entire table. It becomes tedious doing every month when I have to add different keywords. Ideally, I would like to add 10 lines to the referenced list, but only need 2 of them. That way, I don't have to edit the formula next time. The hiccup is that the formula will not return "Unknown" unless the range in the formula has data all the way to the end. If there is any blank spaces in the table, it just returns "0".

I have a feeling I am over thinking this, but how do I add to my formula to return "Unknown" regardless if my keywords table has data all the way to where I have specified or not?

Thanks in advance.

 

[KRice]

Here is one idea since you appear to be using Excel 365. Convert your lookup table to a formal table (click in table and hit Ctrl-t and indicate you have headings...give the columns heading names first) then use structured references in your formula. You can add to the bottom of this reference table without needing to update the range in the formula. And then use the FILTER function to filter out any blanks.

MrExcel_20220608.xlsx
	A	B	C	D	E	F	G
1
2	Date	Vendor	Amt	Cat		Vendor	Category
3	5/2/2022	Chili's 5/1	100	Unknown		McDonald's	Food
4	5/2/2022	McDonald's F44	10	Food		ExxonMobil	Car Expenses
5	5/2/2022	Target 5/1	60	Miscellaneous		Target	Miscellaneous
6	5/2/2022	ExxonMobil 973	90	Car Expenses
7
8
9
Sheet4

Cell Formulas
Range	Formula
D3:D8	D3	=IF(B3="","",FILTER(Table1[Category],(Table1[Vendor]<>"")*(ISNUMBER(SEARCH(Table1[Vendor],B3))),"Unknown"))