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")

 

[Fluff]

Ok.
Firstly I would suggest getting rid of the blank rows in the TransKW table, that is what is causing the problem & it defeats the whole point of having a table.
Secondly do you need to do a partial match, or just an exact match?

 

[Austin Lang]

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

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

This formula worked! Thank you so much!

 

[Legacy 143009]

Glad it did help! Thanks for the feedback

 

[Austin Lang]

Ok.
Firstly I would suggest getting rid of the blank rows in the TransKW table, that is what is causing the problem & it defeats the whole point of having a table.
Secondly do you need to do a partial match, or just an exact match?

I do know the blank cells are the issue. I am attempting to make this file easily usable for people who are not as excel-inclined. I wanted to the blank spaces so keywords can be easily added. They are partial matches, but FlashBond's formula worked for the issue. I appreciate the assistance!

 

[Fluff]

They are partial matches, but FlashBond's formula worked for the issue.

Except that formula will not find partial matches.