Adding categories to bank statement download

[onthecauseway]

I have downloaded by bank statement into excel and want to add categories to each line.
The statement download provides information in column D sheet 1 similar to

CARD PAYMENT TO TESCO-STORES-6352,4.32 GBP, RATE 1.00/GBP ON 13-11-2015
CARD PAYMENT TO CARPHONE WAREHOUSE, XXXX GBP, RATE 1.00/GBP ON 07-11-2015
DIRECT DEBIT PAYMENT TO VIRGIN MEDIA PYMTS REF XXXXXXX

My thought is to add a lookup list on sheet 2 which will add categories onto sheet 1 column G

TESCO = GROCERIES
CARPHONE = MOBILE
VIRGIN MEDIA = ENTERTAINMENT

I have tried using VLOOKUP but without success.
Can anyone point me in the right direction?

 

[lhartono]

Try this array formula ( ctrl+shift+enter ):

=IFERROR(INDEX(Sheet2!$B$2:$B$4,MATCH(INDEX(Sheet2!$A$2:$A$4,MATCH(1,COUNTIF(D2,"*"&Sheet2!$A$2:$A$4&"*"),0)),Sheet2!$A$2:$A$4,0)),"")

 

[Spotycus]

Hi, After reading your post, I think I might be able to help you but I would be interested to learn a little more about what you plan to do with the data after you assign the categories. My background is accounting and my primary area of study has been focused on the bank memo and finding ways to manipulate the data to reduce the amount of data entry that I have to do. For example, if you happen to be working with QuickBooks Desktop, checkout Vendorsync (www.vendorsync.co/beta) an application we are about to release for beta testing that enables a QuickBooks Desktop user to eliminate the need to assign a category to each transaction just by assigning a payee name.

Even if you plan to take the data to some other program, I may still be able to help you with one of my other excel widgets. I stated down the road your thinking and you would be amazed how large your list can quickly become! I will send you a private message also with my contact information but you can also find it on my website https://parkway.business. Either way I am happy to spend some time with you to see if I can be of assistance!

Matthew Fulton
Parkway Business Solutions