# Budget Tracker Issues



## Austin Lang (Tuesday at 3:05 PM)

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


----------



## Flashbond (Wednesday at 12:51 AM)

Try like:

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


----------



## Fluff (Wednesday at 7:02 AM)

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


----------



## Austin Lang (Wednesday at 11:20 AM)

Fluff said:


> 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 (Wednesday at 11:24 AM)

Flashbond said:


> 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 (Wednesday at 11:29 AM)

Ok, how about

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


----------



## Austin Lang (Wednesday at 11:37 AM)

Fluff said:


> 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 (Wednesday at 11:41 AM)

Which image contains the formula?


----------



## Flashbond (Wednesday at 11:44 AM)

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

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


----------



## Austin Lang (Wednesday at 11:48 AM)

Fluff said:


> Which image contains the formula?


Blue Table, Category.


----------



## Austin Lang (Tuesday at 3:05 PM)

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 (Wednesday at 11:50 AM)

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 (Wednesday at 11:50 AM)

Flashbond said:


> 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!


----------



## Flashbond (Wednesday at 11:51 AM)

Glad it did help! Thanks for the feedback 👍


----------



## Austin Lang (Wednesday at 11:52 AM)

Fluff said:


> 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 (Wednesday at 11:59 AM)

Austin Lang said:


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


Except that formula will not find partial matches.


----------

