Partial match lookup question

[drink.the.koolaid]

Hello excel gurus. I've got a spreadsheet of my bank transactions. If
the transaction description contains a keyword from another list, I'd
like to assign it a category from the next column over.

For example:

Sheet1 has transaction descriptions like:

POS DB WAL-MART # 9100 08/09 1030 HUNTERS CO
THE OLIVE GARD KNOXVILLE TN
POS DB TARGET T20 9100 08/04 4700 NEW HARVES


Sheet2 has a list of keywords and categories like:

WAL-MART Entertainment - Toys
TARGET Entertainment - Toys
OLIVE GARD Food - Dinner


I was using vlookup but there are problems because many vendors include
the transaction date in the description like the walmart and target
items above.

Any suggestions?

 

[Dave Peterson]

=INDEX(Sheet2!$B$1:$B$10,
MIN(IF(ISNUMBER(MATCH("*"&Sheet2!$A$1:$A$10&"*",A1,0)),
ROW(Sheet2!$A$1:$A$10))))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

 

[Lori]

=LOOKUP(99,SEARCH(Sheet2!$A$1:$A$10,A1),Sheet2!$B$1:$B$10)

(Note: if there's more than one matching category this gives the last
match.)

 

[Guest]

Maybe this?:

With
The list on Sheet2, beginning in cell A1
* no match
wal-mart Entertainment - Toys
target Entertainment - Toys
olive gard Food - Dinner

Note: I added the asterisk item (*) at the TOP of the list.

Then....on Sheet1
A1: POS DB WAL-MART # 9100 08/09 1030 HUNTERS CO

B1:
=LOOKUP(10^99,MATCH("*"&Sheet2!$A$1:$A$10&"*",A1,0)/(Sheet2!$A$1:$A$10<>""),Sheet2!$B$1:$B$10)

Note: That formula is durable against unmatched items (e.g. new accounts).
It returns "no match" in those instances.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Ron Coderre]

My apologies, Lori....I intended to respond to the OP, not your post.

Regards,
Ron

 

[drink.the.koolaid]

=LOOKUP(10^99,MATCH("*"&Sheet2!$A$1:$A$10&"*",A1,0)/(Sheet2!$A$1:$A$10<>""),Sheet2!$B$1:$B$10)


Perfect! Thank you so much... Would you mind explaining how it
works? I understand the MATCH part, but what's the division for?