Vlookup Partial Text - Transactions from Bank Statement

**jowettluke** · 12-29-2015, 11:48 PM

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 table array 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".

I currently have the below formula, however this only works when the transaction description only contains "Snapdragon".

=VLOOKUP("*"&A1&"*",B:C,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

**FDibbins** · 12-30-2015, 12:18 AM

Hi, welcome to the forum

Looks to me like you are only using A1 in the reference, with no mention of the 2nd category?

In case you are unfamiliar with vlookup(), it searches in the 1st column (and ONLY the 1st column) of the range for your criteria. It then returns something from the specified number of columns across from that.

In your example formula, it will try and find anything with Snapdragon (aaSnapdragon/aaSnapdragonbb/Snapdragonbb) in column B and then return the corresponding entry from column B

**jowettluke** · 12-30-2015, 09:28 PM

Hi Fold,

Thank you for your email.

Currently the vlookup does return the correct category that I want, however it does not return when the value contains more characters that what is in my lookup table.

I have attached my spreadsheet.

The formula in place for M2 is =VLOOKUP("*"&C2&"*",J:K,2,0)

A picture of my spreadsheet is below. The image feature within this reply box didn't work.

http://postimg.org/image/dxdzbocgj/

Thanks,

Luke

**FDibbins** · 12-30-2015, 09:38 PM

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

(I think you forgot to attach your file?)

**jowettluke** · 12-30-2015, 09:49 PM

Apologies, attachment now included within the below link. Thank you for your help.

http://www.filedropper.com/excelforumhelp

**FDibbins** · 12-30-2015, 09:52 PM

Sorry to bounce you around on this, but Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites

**jowettluke** · 12-30-2015, 09:56 PM

My firewall at work isn't allowing me to upload it onto this forum. No problem if you're unable to access the above link. I'll look elsewhere.

Thanks anyway.

**FDibbins** · 12-30-2015, 10:57 PM

I am unable to access pretty much any file-hosting sites due to the company fire wall

Regarding this...

Currently the vlookup does return the correct category that I want, however it does not return when the value contains more characters that what is in my lookup table.

Do you mean you are searching for aabbccddee but in your table you only have aabbccdd?
If so, can you cut down on the characters in your search criteria - maybe with left(a1,8), or some such - or would that return false hits?

If you cannot upload a small file, can you copy/paste a small sample here instead?

**bebo021999** · 12-31-2015, 12:45 AM

Try in M2:

=IFERROR(IF(LEN(TRIM(C2))=0,"No Information",LOOKUP(1,1/IFERROR(ISNUMBER(SEARCH(IF($J$2:$J$53="","ZZZZ",$J$2:$J$53),C2)),0),$K$2:$K$53)),"Check typo in C column")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.