Spend Analysis Categories

[aurelien_21]

Hi all,

I'm working on some spending analysis and I'm struggling with the categorisation of transactions.
I've made a small sample example attached below. I've listed all the suppliers I expect to see transactions from in the 'Ref' sheet, and give each supplier a corresponding category from a finite drop down list.
image.png
In my 'Banking' sheet, I have complex descriptions in column B and I want a formula for cells in column D (Category) to tell me which category each transaction falls under based on key words/characters from the transaction description. To make this extra robust, i've thrown in some suppliers with concatenated endings so I assume wildcards (*) will be more appropriate than simply reading the entire string of supplier names.

Any help would be greatly appreciated.

[torachan]

why make things difficult when you could just use one sheet and categorize the items as you enter your banking data ?????

[aurelien_21]

Hi, the reason is that I import my banking data 300 rows at a time, and at the moment I have over 3000 rows of transactions. As these are made up of around 50 suppliers, inputting categories 3000 times would be inefficient ..

[AliGW]

In D2 copied down:

=IFERROR(LOOKUP(1000,SEARCH(Ref!$A$2:$A$5,B2),Ref!$B$2:$B$5),"")

[aurelien_21]

Excellent, THANK YOU !

[AliGW]

You're welcome!