Assign a value to a cell based on a portion of the content of another cell

Hi all,

I'm trying to create a budget and I'd like to create a formula that will automatically categorize my transactions. I already have a working formula started, but I'll reach the maximum character allotment before including all of the categories. Here's a shortened example. The transportation category alone would need to reference 50+ cells from sheet1 instead of the two shown in the example.

Formula:

Please Login or Register  to view this content.

While a lot of my transactions will be repetitive, I'll obviously shop at new places every year. Having a separate sheet of keywords will give me the flexibility to include these new businesses in the search without having to change the formula. I'll have placeholder keywords of randomly typed text that will never return a positive result when searching the transaction list. Then when I shop at a new place, I can substitute that text with the relevant text from the transaction description. Is there another way that I can go about this that will use less characters? I can probably fit about 70% of the current formula before I run out of characters.

Any help would be greatly appreciated. Thanks in advance!

Brian

I'm not sure how that would work. How would I create a table with my example? In the example you provided everything is static. Everything has one product code and price. But for my example, every category has multiple key words, and every key word will reference multiple transactions.

Ok, here's the bare bones of what I'm doing here. My transactions download in 5 columns, A,B,C,D, and E. What I would like to do is categorize and sum these transactions by category. I would like a formula that automatically assigns a category if I've shopped there before. As you can see in columns F and G, I do have a working formula, it's just too long once I get all of my categories and keywords listed. If only there was a way I wouldn't have to list "ISNUMBER" so many times. I was hoping there would be a way to only use "ISNUMBER" once per category instead of once per keyword. Basically tell it to search all these keywords, and if one of them is a number, then spit out the corresponding category. Hope that made sense...

I may have a band-aid solution though. I can fit as many categories as I can into a formula, and when it comes up "False", I can have it return "Continue". Then in the next column I can do =IF(F2="Continue",yada yada yada... Do you see an easier solution to sum by category, or should I just run with my band-aid fix? Thanks!

Thanks, but unfortunately I'm still a bit lost. I've only used lookup formulas in limited fashion before and they were always for exact matches. What value would I be looking up, Transactions!C2? Or the range of cells in column C from your table?

Awesome, that's perfect!! I kept the table on a separate sheet from the transactions and used this formula for categories:

Formula:

Please Login or Register  to view this content.

And this one for subcategories:

Formula:

Please Login or Register  to view this content.

This also gives me the benefit of being able to use a ton of placeholder cells so I should never have to edit my formula no matter how many different businesses I have transactions with. Would you mind explaining what the 1E100 is though?

I was able to find what the 1E100 is with a little research, but I'm still having trouble understanding how this formula actually works. I get that the 1E100 is a larger number than can be returned by any of the searches because of character limits for each cell, but I don't see why it's returning a value from the table. Out of curiosity I changed it to a low number to see what would happen and I noticed that if the lookup# is greater than or equal to the starting position of any positive result, it will return the table value. If it's lower than the starting position, then it returns the error value, or in this case a blank cell. Why is that?