How to lookup a partial text string

[joh_ver]

I download my bankstatement lines and want to automatically link them to categories. I'm lazy, don't want to manually apply them.

bankstatement-description lines look like this:

"withdrawal from cash machine 7/12/2016"
"pin payment in supermarket 7/12/2016"
"health insurance for december"
"payment of salary dec16"
etc.

I have a table in Excel for the categories, based on specific words in the description:
searchword - category
withdrawal - daily expenses
cash machine - daily expenses
supermarket - shopping
health insurance - insurance
salary - salary

Now I want a new column in my download table which compares the description field with the category table. I was trying the vlookup and search functions, but couldn't figure it out. Hope you can!

Thanks,
Johan

[PeteABC123]

Might use =SUMIF. . .surround your category names with asterisks: *withdrawal*, allowing for additional text in your lookup range.
Pete

[José Augusto]

Hi

Considere A2:B7 a table with (searchword, category) where row 2 are labels
Also considere E3:E10000 the bankstatement

Use this formula in F3 and copy down

Please Login or Register  to view this content.

See the file

[Phuocam]

Or try this ...

=IFERROR(LOOKUP(2,1/COUNTIF(E3,"*"&$A$3:$A$7&"*"),$B$3:$B$7),"")

[Tony Valko]

Or try this ...

Data Range

	A	B	C	D	E
1	Transaction	Category	------	Searchword	Category
2	withdrawal from cash machine 7/12/2016	daily expenses		withdrawal	daily expenses
3	pin payment in supermarket 7/12/2016	shopping		cash machine	daily expenses
4	health insurance for december	insurance		supermarket	shopping
5	payment of salary dec16	salary		health insurance	insurance
6				salary	salary

This formula entered in B2 and copied down:

=IFERROR(LOOKUP(1000,SEARCH(D$2:D$6,A2),E$2:E$6),"")

You may have to replace the commas with semi-colons:

=IFERROR(LOOKUP(1000;SEARCH(D$2:D$6;A2);E$2:E$6);"")

[joh_ver]

Thanks a lot for the quick and working functions!
Enjoy your weekend,
Johan

[Tony Valko]

You're welcome. Thanks for the feedback!