Categorize credit card transaction details by predefined buckets (e.g. gas, food, entertainment, etc.)

[JamesonMH]

Hi folks,

I want to do a pivot table based on credit card transaction data that is categorized - obviously the raw transaction data won't make for a very good pivot table since it's not categorized... When downloading a credit card stmt in csv I get transaction data something like below (blank fields not relevant):


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Card #[/TD]
[TD]Trans. Date #[/TD]
[TD]Posting Date[/TD]
[TD]Amount[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]ABC Car dealership[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25[/TD]
[TD]#123 Woolmart[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]XYZ Gas Mart[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]125[/TD]
[TD]ABC Restaurant[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]75[/TD]
[TD]ZYZ Liquor Store[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]Costking #1234[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]40[/TD]
[TD]Lot ABC Parking[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD]Mcdavid's #56568[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]80[/TD]
[TD]ABC Auto Park[/TD]
[/TR]
</tbody>[/TABLE]

I want a lookup formula that allows me to put everything into say 1 of 5 predefined buckets (i.e. fast food, auto, entertainment, department stores, other) so I can then do a pivot table that's clear.

"other" would just be a catch all for things that didn't match anything in the master table.

So based on my predefined master table, if the transaction description field contained the word "Costking", it would go under "department stores". And "ABC Car dealership" would fall under "Auto" (since my master table would have Auto next to Costking.

Any ideas?

Thanks
James