Budget Tracker Issues

[Austin Lang]

Hi all,

I am currently doing an overhaul to my Budget/Bills Tracker. I have created a table that reads transactions and gives every transaction a category to be later totaled based on those categories. It used to work because the "table" of categories was not an actual table but was just listed on the page and I was able to specify the exact cell range to look to for the categories. I have now made the change so the information is in a table so more can be added easily and referencing a table is easier. The issue is if a transaction comes through that it doesn't recognize, it is showing "0". This means it is looking at the blank spaces in the table instead of "erroring out" like I want it to. How would I go about making it ignore the blank spaces in the table and "error out"? I attempted to add a simple IF statement to change "0" to "Unknown" but that creates a circular reference.

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

New Formula:
=IF(ISBLANK($M5),"",IFERROR(INDEX(TransKW,MATCH(TRUE,ISNUMBER(SEARCH(TransKW[Keyword],$M5)),0),2)),"Unknown")

 

[Legacy 143009]

Try like:

=IFERROR(INDEX(TransKW,MATCH(1,ISNUMBER(SEARCH(TransKW[Keyword],$M5))*($M:$M<>""),0),2)),"Unknown")

 

[Fluff]

Are the values in col V numbers or text?
Also what is the name of that column?

 

[Austin Lang]

Are the values in col V numbers or text?
Also what is the name of that column?

Both column U and V were text. U is the keyword it is looking for, and V is the category. The new columns are "Keyword" and "Category".

 

[Austin Lang]

Try like:

=IFERROR(INDEX(TransKW,MATCH(1,ISNUMBER(SEARCH(TransKW[Keyword],$M5))*($M:$M<>""),0),2)),"Unknown")

This doesn't quite work because now everything is labeled as "unknown". It must not be referencing the table correctly.

 

[Fluff]

Ok, how about

=IF(ISBLANK($M5),"",IFERROR(INDEX(TransKW[Category],MATCH(TRUE,ISNUMBER(SEARCH(TransKW[Keyword],$M5)),0)),"Unknown"))

 

[Austin Lang]

Ok, how about

=IF(ISBLANK($M5),"",IFERROR(INDEX(TransKW[Category],MATCH(TRUE,ISNUMBER(SEARCH(TransKW[Keyword],$M5)),0)),"Unknown"))

The formula functions in returning categories, but it still returns 0 instead of "Unknown"

If this helps, a snip of what I am looking at. Simplified.

 

[Fluff]

Which image contains the formula?

 

[Legacy 143009]

Despite Fluff's suggestion, I will insist on my solution:

=IF(M5<>"",IFERROR(INDEX(TransKW[Category],MATCH($M5,TransKW[Keyword],0)),"Unknown"),"")

 

[Austin Lang]

Which image contains the formula?

Blue Table, Category.