Vlookup Partial Text - Transactions from Bank Statement

[jowettluke]

Hi All,
I'm currently trying to group my bank transactions into categories using vlookup. This is so I can understand where all my money is ending up!
The value is the transaction description, for example, "4835-*-*-1003 Df Snapdragon"
My vlookup ttable has been set out into two columns. One column being a key word within the above transaction description "Snapdragon". The second column is a category description "Drinking Out", which is what I want it to return.
I currently have the below formula, however this only works when the transaction description only contains "Snapdragon".
=VLOOKUP(""&C2&"",J:K,2,0)

Please could I grab some help on this one, as I can't find a workaround where the vlookup finds a partial match and returns the desired field. For any clarification, just give me a shout!
Cheers,
Luke

 

[Beamer]

Hi Luke, try this:
=VLOOKUP("*"&C2&"*",J:K,2,0)

If you want a listing of all transaction that suit the wildcard, then maybe this sheet will help.

 

[jowettluke]

Hi Beamer, thank you for your response and help with my query.

The spreadsheet you have attached isn't what I'm looking to have set up. I hope I can clarify my request below, apologies if it doesn't:

Column A would contain my transaction descriptions containing numbers, words and nonsensical data that I have sourced from my bank statement. For example: "Sierra Cafe Shortlan Card number: 4835 **** **** 1003"

Column B would contain a keyword that I would have sourced from each field in column A. In the above example, this would be Cafe.

Column C would be a category for each field in column B. For instance, 'Cafe' categorised as Eating Out.

From having the above set up, I would be able to understand how my expenses are divided up. The issue I am having at the moment is that Column A contains various amounts of data, and I cannot find a partial text vlookup search for finding specific words that are listed in column B. I have outlined another example below for further clarity on my request:

A2: 4835-43-99-1003 DF Landlord Ltd
B2: Landlord
C2: Bills

A3eterpans Adventure Card number: 4835 **** **** 1003
B3: Peterpans
C3: Travel

A4: 4835-43-58-1003 Powershop Df
B4: Powershop
C2: Bills

Thanks in advance for your help

Luke

 

[Beamer]

Hi Luke, I think this will be getting you closer.

I have a table of keywords and categories in columns G:H which could be on a separate sheet if you preferred.
Column C displays the keyword that was found in the list (if you don't need it then just delete the column).
Column D shows the matching category from the table.

I also added another little table adding up the category totals just in case it might help you.

If I am still misunderstanding what you are looking for, could you lease upload a small sample sheet showing the results you expect?

BTW, formulae in columns C & D are array formulas so need to be entered by holding down Ctrl+Shift while pressing Enter.

 

[jowettluke]

Thanks Beamer, just testing it out at the moment and each field is appearing as *Unknown Transcation*. This site isn't letting me upload my excel file, so I'll PM you a link to it, if that's ok.

 

[Beamer]

Not sure if you got my message, I've had trouble with it from time to time.

I couldn't seem to get your file. But by the sounds of it, you weren't entering them as array formulas.
As in the last post... After you press F2 or click on the edit bar or change the formula, you need to hold down Ctrl+Shift and then press Enter..all 3 buttons together.

Hopefully that will get you up and running.