0

I have the same problem as mention in the post How do I use Excel to categorize bank transactions into easily understandable categories to see where my money is going?

I've tried the formula given but it doesn't always work.

This is when it won't work, text search:

"Restaurant Baton Rouge"
"Baton Rouge DRUMMONDVILLE"

I want those 2 sentences to be categorised as "Restaurant". But the formula is searching for the line number that match with EACH word found in the "Text search" colomn. So if the category "Restaurant" has been found on line 20 and "Baton Rouge" on line 5, it will return the value of the line 25 (20+5).

Is it possible to stop searching when excel find the first match ?

Máté Juhász
  • 22,071
Lucky Luke
  • 1
  • 3

1 Answers1

1

For a simple but effective solution, you could try a VLOOKUP function.

Taking the example from your original question:

Hopefully you'll see an example screenshot here

I'm not sure how it would work with a substring contained in a cell but you could just create a table of categories where you contain many possibilities of the same cell value which would return the right category.

EG:
Restaurant Baton Rouge = Restaurant
Baton Rouge DRUMMONDVILLE = Restaurant

This approach has its advantages, for example, when you have many Paypal transactions. A transaction description often has "PAYPAL *vendor", so you'd retrieve the wrong categories if you were just searching for the "PAYPAL" substring.

The syntax of a VLOOKUP function is =VLOOKUP(lookup value, lookup range, column number for return value, match type). You can refine this to suit your needs. For example, my real-life version uses names and table references but does the exact same thing as above. =VLOOKUP([@[Transaction Description]],tblCounterparts,2,FALSE)

Considering that you want to keep adding to the list of categories list and modifying it, you could investigate named ranges and the offset function vary its size.

Stephen Rauch
  • 3,296
KMinnieA
  • 36