Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Lookup question | Excel Worksheet Functions | |||
Partial String Match & Wild Cards Using VLOOKUP | Excel Worksheet Functions | |||
can lookup return err if no match found | Excel Worksheet Functions | |||
Partial String Match Using VLOOKUP | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions |