Categorizing budgets

[humility36]

Good Morning Team,

I'm new here and I've been stuck on this issue all weekend. I've tried to figure this out on my own, but I just can't get the logic down. I'm really hoping someone can help me out here.

I've been tasked with categorizing an expense report which is nothing more than a .csv transaction dump from a bank. I've converted the csv to excel and created a table of possible categories. If the transaction description has a specific keyword "like fuel, gas, speedway" than I want the category to say "gas". I've tried vlookup and index and match and nothing is working right. Please see my example below.

	A	B	C	D	E	F	G
1	Date	description (1. search this column)	withdrawal	deposit	category (4. write the category that matched a keyword)	Lookup keyword (2. find a matching keyword)	lookup category (3. find matching keyword and use this category in the category column)
2	11/23	speedway gas pos purchase seq# 8493	15			gas	gas
3	11/24	subway restaurant	10.99			fuel	gas
4	11/24	aep electric	85			speedway	gas
5	11/25	kroger fuel	25			aep	electric utility
6	11/26	paypal payment		60		subway	food
7						paypal	money in - see comments
8						mortgage	mortgage
9						starbucks	food
10

I don't know how I would write this logic. I tried doing an array search, but I keep getting a reference error or a #N/A error. My logic is to search the description column for a keyword in the look up table and if there is a match then write the associated category in the category field. Now, I did have a lot of success with using nested if statements, but I have more than 64 nested if statements and I couldn't add any more key words. So now I'm limited to just 64 keyword, not to mention my formula looks out of control and messy.

Sample of my nested if statement. (I want to move away from this because it does not seem like the most effcient and I'm limited to only 64.)

=IF(ISNUMBER(SEARCH("*culligan*",E:E)),"culligan",IF(ISNUMBER(SEARCH("*spotify*",E:E)),"spotify",IF(ISNUMBER(SEARCH("*aqua*",E:E))," water bill",IF(ISNUMBER(SEARCH("*walmart*",E:E)),"grocery",IF(ISNUMBER(SEARCH("*My company*",E:E)),"paycheck",IF(ISNUMBER(SEARCH("costco gas",E:E)),"gas",IF(ISNUMBER(SEARCH("speedway",E:E)),"gas",IF(ISNUMBER(SEARCH("fuel",E:E)),"gas",IF(ISNUMBER(SEARCH("columbia gas",E:E)),"gas utility",IF(ISNUMBER(SEARCH("meijer",E:E)),"grocery","other"))))))))))

Any help would be appreciated I have to do some end of the year reporting and this was assigned to me last minute all because I thought I would be nice and I was able to write 3 basic formulas for my manager who was struggling with some manual data entry in his spreadsheet. (*facepalm*)

Thanks everyone for your help in the future,
Humility36

 

[Fluff]

Hi & welcome to MrExcel.
How about
=INDEX($G$2:$G$9,AGGREGATE(15,6,(ROW($G$2:$G$9)-ROW($G$2)+1)/(ISNUMBER(SEARCH($F$2:$F$9,B2))),1))

 

[humility36]

@Fluff,

Thank you so much for your prompt reply! Your solution is working great! Is it possible to enhance this just a little bit? What I'm finding out is if there is no keyword available, the cell calculates to "0". Instead of the cell being zero (0), can I modify the formula to say the text "other". I'm finding that there are some transactions that do not fit in any of the categories I've been given and so I need to dump them into an "other" category to follow up on.

My end goal here is to get the data formatted and configured to then move it to some pivot tables and reports.
Thanks again for your swift response! It's really helped me get my day up and running on this.

Respectfully,
Humility36

 

[Fluff]

If there is no keyword match you should get a #NUM! error. If you are getting a 0 that suggests a match was found, but col G did not have a value.

You can deal with the no match like
=IFERROR(INDEX($G$2:$G$9,AGGREGATE(15,6,(ROW($G$2:$G$9)-ROW($G$2)+1)/(ISNUMBER(SEARCH($F$2:$F$9,B2))),1)),"other")