Partial match lookup question
 

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?

Partial match lookup question
 

=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.


wrote:

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

Partial match lookup question
 

=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.)

wrote:

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?

Partial match lookup question
 

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


"Lori" wrote:

=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.)

wrote:

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?

Partial match lookup question
 

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

Regards,
Ron

"Ron Coderre" wrote in message
...
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


"Lori" wrote:

=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.)

wrote:

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?

Partial match lookup question
 

=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?