Search table for string return next columns value

[mikpits]

Some of you folks out here are wizards so I thought I'd ask you.
I keep chipping away but this is going to take me weeks or months.

I'm trying to create a home budget

After downloading my statement from my bank I'd like to automatically
assign the category that the transactions belong to.

I have a list of payees that are assigned specific categories ie:
(payees!A1 Payees!B1)
Payees, Categories
Wal-mart , Household
Costco, Groceries
Costco Gas, Gasoline
Allstate, Insurance
Dennys, Eating out
albertsons, Groceries
Mervyns, Clothing
etc


The Transaction_detail comes in with a bunch of stuff ie:
(in worksheet January column B5)
DENNY'S #6739 SOUTH JORDAN UT Date 11/14/06 2433333432240004760575
5814
MERVYNS 00002949 SANDY UT Date 11/14/06 24333376288591142946824 5355
BURLINGTON COA00000794 MURRAY UT Date 11/14/06 24399006288320200161199
5651
JCPENNEY STORE 0231 SANDY UT Date 11/14/06 2433316628823423742798 5355
POS ALBERTSONS 370 E. 200 S. SALT LAKE CIT UT
POS COSTCO GAS #00487 11100 S. AUTO MALL
BLOCKBUSTER VIDEO #49057 DRAPER UT Date 10/17/06
24610436234232018594403 7841
REAMS FOOD STR.INC. SANDY UT Date 10/18/06 24433336291040001974168
5551


How do I take the payee, search the transaction detail and return the
category in the same row as the match of the payee in the transaction
detail?

For example I'd like:

Eating out, DENNY'S #6739 SOUTH JORDAN UT Date 11/14/06 ....
Clothing, MERVYNS 00002949 SANDY UT Date 11/14/06 ....
clothing, JCPENNEY STORE 0231 SANDY UT Date 11/14/06 ....
Groceries, POS ALBERTSONS 370 E. 200 S. SALT LAKE CIT UT
Gasoline, POS COSTCO GAS #00487 11100 S. AUTO MALL
entertain., BLOCKBUSTER VIDEO #49057 DRAPER UT Date 10/17/06 ....
etc.

I can do the vlookup, but I can't figure out the search in string
thing.

Thanks for your help

 

[T. Valko]

Here's a sample file:

http://cjoint.com/?blxBoBJBrQ

sample_lookup.xls 13.5kb

This should get you started. There are some things to consider.

If you download the file you'll notice that cell A1 returns #N/A even though
"Denny's" is listed as a payee. The difference is that in cell B1, "Denny's"
contains an apostrophe and "Dennys" in the list of payees does not.

You'll also notice that cell A6 returns "Groceries" for "Costco" while cell
B6 contains "Costco Gas". The reason that happens is because the formula
stops when it finds the first match. In the Payee list Costco is listed
before Costco Gas so the formula stops when it matches Costco in cell B6 to
Costco from the list. Where there are these types of "fuzzy" matches list
Costco Gas before Costco:

Payees, Categories
Wal-mart , Household
Costco Gas, Groceries
Costco, Gasoline
Allstate, Insurance
Dennys, Eating out
albertsons, Groceries
Mervyns, Clothing

The other #N/A's are due to no matches (incomplete payee list)

Biff

 

[mikpits]

You are a genius! That's exactly what I was looking for but couldn'
figure it out.

Thanks so much for your help and tips.

I'm incorporating it into my budget now.

again thanks.
---------

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

You are a genius! That's exactly what I was looking for but couldn't
figure it out.

Thanks so much for your help and tips.

I'm incorporating it into my budget now.

again thanks.