If cell contains text, VLOOKUP that text

[cogough]

Hi all,
Apologies if this has been answered somewhere else but if it is, I can't find it.

I'm setting up a budget spreadsheet where I download all of my transactions from my bank and run a little macro that automatically categorizes all of my transactions based on an existing table. So for example, if a transaction description is "Debit Card Purchase - KROGER 800 GLENWOOD RD", I want the next column to pull in Kroger as the description and the column after to pull in Groceries. For now, I'm just looking for the actual formula that will accomplish before starting the macro.

I've found what I originally thought were solutions using LOOKUP/SEARCH/ISNUMBER but that seems to be the opposite of what I'm trying to do.

Below is a simple version of what I need. The formula I'm looking for would be entered in the second and third columns.

Thanks in advance!

Bank Transaction Description	Clean Description	Category
Withdrawal from PENNYMAC ACH PYTS	PennyMac	Mortgage
Debit Card Purchase - RELISH SALON 924	Relish	Haircut
Debit Card Purchase - KROGER FUEL CTR	Kroger	Fuel
Digital Card Purchase - FANDANGO COM FANDANGO COM CA	Fandango	Entertainment
Debit Card Purchase - CVS/PHARM 07336--520 B	CVS	Pharmacy
Debit Card Purchase - REGAL CINEMAS HLYWD 24	Regal Cinemas	Entertainment
Debit Card Purchase - AMAZON MKTPLACE PMTS W WWW AMAZON C WA	Amazon	Misc
Debit Card Purchase - SQU SQ DRIP COFFEE SH	Drip	Food
Debit Card Purchase - PP FOODA CHICAGO IL	Fooda	Food
Debit Card Purchase - KROGER 800 GLENWOOD RD	Kroger	Groceries
Debit Card Purchase - KROGER 150 MAIN ST	Kroger	Groceries
Debit Card Purchase - KROGER 233 DECATUR AVE	Kroger	Groceries

<tbody>
</tbody>

Lookup	Clean Description	Category
amazon	Amazon	Misc
cvs	CVS	Pharmacy
drip coffee	Drip	Food
fandango	Fandango	Entertainment
fooda	Fooda	Food
kroger fuel	Kroger	Fuel
kroger	Kroger	Groceries
pennymac	PennyMac	Mortgage
regal cinema	Regal Cinemas	Entertainment
relish	Relish	Haircut

<tbody>
</tbody>

 

[iliace]

This looks somewhat tricky.

For example, you have two Clean Description items that pull in as "Kroger". Both contain "kroger" in the Lookup field. However, the Category column is differentiated by whether the "kroger" is followed by "fuel". Simple enough to rectify for the Lookup table, but when you get back to your transaction-level detail, to determine whether or not a "kroger" value is actually "Food" or "Groceries" category, you need two checks. This is not the case for the rest of your table. Furthermore, if you had a third category from the same provider (e.g. "kroger furniture") then you would need a third check. Based on the subset of data you provided as example, I do not think there is a good/reliable formula you can write to handle this.

Perhaps you need to expand your categories so that each Lookup can be uniquely identified. In other words, you should have a category for "kroger fuel" as you do now, then "kroger 500", "kroger 800", and any other Kroger stores you patronize for groceries identified by the address. As it stands, any standalone "kroger" category by itself will also match "kroger fuel", which is not what you want.

Does your current little macro work as intended? Can you post that code?

 

[Aladin Akyurek]

In B2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&INDEX(Table,0,1)&" "," "&A2&" "),INDEX(Table,0,2))

In C2 enter and copy down:

=VLOOKUP($B2,Table,3,0)

where Table is the colored table you posted, which contains amazon >> Amazon >> Misc, etc.

 

[cogough]

Aladin, that was brilliant! I would have never been able to come up with that. Thanks

I just had to remove the two spaces before and after the first index because after evaluating it, I found that it was looking for " cvs " but the cell contained " cvs/". After that tweak, it worked on every cell. Here's my final formula.
=LOOKUP(9.99999999999999E+307,SEARCH(INDEX(Table1,0,1)," "&A6&" "),INDEX(Table1,0,2))

iliace, I knew things like Kroger would be a problem but now that I have the basic formula, I can work around that. When I create the macro, I think I'll just add a function to find/replace those problem children so Kroger Fuel becomes Krg Fuel before doing the formula. Those exceptions should be few and far between so it'll be manageable.

 

[Aladin Akyurek]

@ cogough

You are welcome. Thanks for the update.