vlookup on key words within text to search

[mecannon1]

I have this dilemma,

On sheet 1 of my workbook I have a list of checking account transactions. It looks like a typical check register (Date, Description, Amount, Balance)

I would like to add a 5th column called Category. I want this column to use a formula that takes the Description text and compares it to a list on sheet 2 that has two columns: (KeyWord, Category)

The KeyWord column would have text values that might exist in the Description column from sheet 1. If a Description contains one of these values then the Category value from the list is returned to the Category column on Sheet 1

Sheet 1 might have a transaction with a description of
CHECK CRD PURCHASE 04/09 WELLS FARGO PREPAID SAN FRANCISCO CA 7020

Sheet 2 has the lookup list of
KEYWORD | CATEGORY
Wells Fargo | Credit Card
ATT | Phone Bill

So the formula should discover that the Description contains Wells Fargo and return Credit Card for the Category value. These Descriptions are coming from my online banking so they have date stamps in them and all sorts of extra text. I have to match on keywords within the description.

This seems to be very challenging scenario since I can't find anyone on the internet with solutions to it. If someone can solve this for me I would be extremely grateful!

 

[John Davis]

I know this is not the best approach, and someone should come up with a better idea.

1) Use a wildcard vlookup on Sheet2 to pull the information from Sheet1

Maybe like in Column C =VLOOKUP("*"&A2&"*" Sheet1$A$2:$A$1000,1,FALSE)

2) Copy and pastespecial values in Column C


3) Insert a Column A on Sheet2 - Copy and paste Column D (was Column C) into the new column A.

4). Now you can VLOOKUP in Sheet1 from Column A to pull the data.

 

[mecannon1]

Thanks for the reply. I appreciate you digging in to help me on this. Since I have many transactions that could match one category, each of the transactions might have a different date stamp for instance, I would be limited when doing a vlookup from Sheet 2 with Vlookup's approach of finding only the first occurance. I have over a thousand transactions on Sheet 1 and about 10 rows on Sheet 2 defining the various categories. Also I was hoping to have things a little more automated so that if I paste a few more transactions on sheet 1 then the category is auto discovered.

I was trying to make use of wildcard vlookups before posting this thread but it seems that vlookup is wired to flow one way WildCardTableValue --> TableArray instead of ExactTableValue --> WildCardTableArray.

There has to be some trick to breaking up the Description into its individual word components and passing to a vlookup in one pass. I looked into Array Formulas since I wanted to pass an array of individual words from the Description but couldn't make sense of how to do it.

Again, thanks for the reply but if there is anything else you or others could share to further automate this it would be very helpful to me.

 

[T. Valko]

I have this dilemma,

On sheet 1 of my workbook I have a list of checking account transactions. It looks like a typical check register (Date, Description, Amount, Balance)

I would like to add a 5th column called Category. I want this column to use a formula that takes the Description text and compares it to a list on sheet 2 that has two columns: (KeyWord, Category)

The KeyWord column would have text values that might exist in the Description column from sheet 1. If a Description contains one of these values then the Category value from the list is returned to the Category column on Sheet 1

Sheet 1 might have a transaction with a description of
CHECK CRD PURCHASE 04/09 WELLS FARGO PREPAID SAN FRANCISCO CA 7020

Sheet 2 has the lookup list of
KEYWORD | CATEGORY
Wells Fargo | Credit Card
ATT | Phone Bill

So the formula should discover that the Description contains Wells Fargo and return Credit Card for the Category value. These Descriptions are coming from my online banking so they have date stamps in them and all sorts of extra text. I have to match on keywords within the description.

This seems to be very challenging scenario since I can't find anyone on the internet with solutions to it. If someone can solve this for me I would be extremely grateful!

Try this...

Sheet2 A2:B3 = list of keywords

Sheet1 A2 = CHECK CRD PURCHASE 04/09 WELLS FARGO PREPAID SAN FRANCISCO CA 7020

Enter this formula in B2:

=LOOKUP(1E100,SEARCH(Sheet2!A2:A3,A2),Sheet2!B2:B3)

Note that if a string contains more than 1 keyword the formula will return the keyword from the keyword list that is nearest the bottom of the keyword list.

 

[mecannon1]

That works. This is awesome! I have excel doing exactly what I want. Now to create the pie chart showing how much I'm spending per category!

Thanks for all the help.

 

[T. Valko]

That works. This is awesome! I have excel doing exactly what I want. Now to create the pie chart showing how much I'm spending per category!

Thanks for all the help.

You're welcome. Thanks for the feedback!

 

[Bowtrain]

I just found this post, and this formula works great on my Excel (Mac, Excel 2011). However I do not understand WHY this works.
Breaking the formula apart, it looks like this:
Find (a very large number, in the 26th character of this string [search() returns 26 for "wells fargo"], and return a value from a vector on another sheet)
I read the descriptions of the syntaxes on the Help page of Excel, but I don't see how this could possibly work (even though it does)!
That embedded search function also doesn't make sense. It seems to return the first vector (and passes it to the Lookup() function) even though it should just return the character number of the search string. How can it return the only the cells of the first vector that are in the string and ignor the others? (It must ignore the others because the 1e100 is forcing the Lookup() to return the last element that is in the string, not just the last element, right?) If it's only returning some of the first vector, the second vector is now a different size than the first vector, which should cause an error in the Lookup() function.
Furthermore, if you break out the Search() function on it's own "=SEARCH(Sheet2!A2:A4,A2)", it errors out (sometimes, not with "wells fargo", but it returns #value if you replace that with "ATT" in the string), so if the search() function errors, how can the lookup be successful? (The lookup() DOES return "phone bill" with "ATT")

Is this a special, built-in functionality of the Lookup() function? Was Lookup specifically designed to work with search() in this manner?

I'd really appreciate an answer, as I hate not understanding why a function works the way it does.

Thanks!

 

[Bowtrain]

Replying to myself!
I think I figured it out. The embedded Search() function is returning a vector (even though it's a little bit invisible) which is the same size as the "Keyword" and "Category" areas (i.e. 2 cells). The search() returned [26;#VALUE!] as a vector -- the Search() found Wells Fargo at character 26, but did not find ATT (#VALUE!). (If both keywords were in the string, then the vector returned by Search() would have been [26; some other number])
Anyway, the Lookup() function is looking up a very large number, 1E100, in the Search() results vector, [26;#VALUE!]. Since Lookup() errors out if the lookup value is smaller than the smallest value in the lookup vector, we're searching for an impossibly large number, which will guarantee a result if there is one, but will always return the last matching item in the lookup vector. So lookup() tries to find 1e100 in the lookup vector (i.e. the search() results), ignores the #VALUE! error and finds 26 in the first cell of the vector. Then the lookup() looks up the corresponding value in the first cell of the "Category" vector.
The Lookup() function is NOT looking up the "Keywords" vector, it's actually completely ignoring it. What I learned is that the Search() function can return an array, even if it's not entered as an {array formula}, and that returned array is acting as the lookup vector.

Biff, if you came up with this yourself, you are a genius for understanding these two functions so well.

 

[jwach]

I just registered for this site to be able to leave this thank you. I know this post is old, but I just spent an hour trying to work this out without luck. This formula worked beautifully. I'm using it for a check register as well. Can't tell you how much I appreciate it. Thank you!